On Fri, Feb 24, 2012 at 3:51 PM, C M <[email protected]> wrote: > 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: >
Suggest you convert to julian day number using the julianday() function, average those, then convert the average back using datetime() or strftime(). > > '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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
