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
