You're trying to calculate it for individual people? Can you count on night-time people to stay night-time, or do you need to worry about someone shifting by 12 hours?
If not, your best bet is, for the night-time people, add, say 6 hours to all of their times, do your average, then subtract the 6 hours back out. So, in your example, 22:00, 23:00, 01:00 would shift to 04:00, 05:00, and 07:00. That average is 05:20. Subtract your 6 hours and you're back at 23:20. There are cases where this will fail, but you might be able to detect data sets that will cause this issue and ignore them. Marc > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of C M > Sent: Friday, February 24, 2012 4:43 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 4:06 PM, Marc L. Allen > <mlal...@outsitenetworks.com>wrote: > > > 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? > > > > Yes, I'm trying to come up with average bedtimes. (Or other once-a- > day-and-usually-in-the-same-general-time-window type human activities). > > So, in my case, I might go to bed at 23:00 one night and then 01:00 the > next, so the "naive average" there would be 23 + 1 = 24/2 = 12:00, or > noon. But of course, to say I was going to bed around noon would not > represent the situation at all. Instead, just as you said, I want in > this case 01:00 to be treated as 25:00, so 23 + 25 = 48/2 = 24:00, or > midnight. > > The thing is, other people might have bedtimes at mid-day, if they work > nights, for example, so it's hard to know how to approach this. > > (One visual metaphor I have is to think about the times on an analog > clock with hands. For 11pm and 1am, the "average angle" of the big > hand is > 12:00am.) > > 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
