I don't think that'll do it. His example shows that, in that specific case, he wants to treat 1:00 as 25:00 for the purposes of averaging, and he also wants to ignore the actual day.
Essentially, he wants to average 22:00, 23:00 and 01:00 and come up with 23:20, in this particular case. To do that, he has to somehow figure out that 01:00 has to be treated like 25:00. Unfortunately, there will be other sets of time where 01:00 will want to be treated as 01:00 and not 25:00. > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Friday, February 24, 2012 4:17 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SELECT average timestamp to get average time of > day? > > On Fri, Feb 24, 2012 at 3:51 PM, C M <cmpyt...@gmail.com> 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 > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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
