I subtract 12 hours...so any time from 24:00:00 to 12:00:00 will work.
Time from noon to noon becomes midnight to midnight. Then you just add the 12
hours back in.
CREATE TABLE tijd(t int(11));
INSERT INTO "tijd" VALUES('2012-02-25 22:00:00');
INSERT INTO "tijd" VALUES('2012-02-27 01:00:00');
INSERT INTO "tijd" VALUES('2012-02-27 23:00:00');
sqlite> select time(avg(time(t,'-12 hour')+12)*3600,'unixepoch') from tijd;
23:20:00
You may pick an offset other than 12 depending on your data.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of C M [cmpyt...@gmail.com]
Sent: Friday, February 24, 2012 2:51 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] SELECT average timestamp to get average time of day?
I'd like to have a SELECT query to get the average time of a person's day
(not necessarily a strict 24 hour day) given timestamps of the form:
'YYYY-MM-DD HH:MM:SS.mmmmmm'. The data will have gaps of days in which
there is no timestamp for that day.
The problem is, simply averaging times of day gets into a problem if times
cross midnight and days are non-consecutive. For example, the average I'd
want from these three timestamps:
'2012-02-18 22:00:00.000000'
'2012-02-19 23:00:00.000000'
'2012-02-28 01:00:00.000000'
Should be 11:20pm, as they are all within a few hours of each other at
night. I have not been able to find a query that produces this.
Thanks,
Che
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
