Sorry.. second example should be: Midnight, Noon, 11:59 PM -> Average around 10 PM
> -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Marc L. Allen > Sent: Friday, February 24, 2012 4:07 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SELECT average timestamp to get average time of > day? > > Actually.... this is quite an interesting question. > > Given two fixed times of midnight and noon, having the third time one > minute before or after midnight drastically changes what I think you > want the answer to be. > > Midnight, Noon, 12:01 AM -> Average around 2AM. > Midnight, Noon, 11:59 AM -> Average around 10PM > > Can you provide more information on what you're trying to do? > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Marc L. Allen > > Sent: Friday, February 24, 2012 4:00 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] SELECT average timestamp to get average time of > > day? > > > > I'm not sure that's possible without more spec. > > > > What is the average time for midnight, 8 AM, and 4PM? > > > > > -----Original Message----- > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > > boun...@sqlite.org] On Behalf Of C M > > > Sent: Friday, February 24, 2012 3:52 PM > > > To: General Discussion of SQLite Database > > > Subject: [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 > _______________________________________________ > 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
