[sqlite] SELECT average timestamp to get average time of day?
C M
(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...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Marc L. Allen
What is the average time for midnight, 8 AM, and 4PM?
Marc L. Allen
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?
Marc L. Allen
Midnight, Noon, 11:59 PM -> Average around 10 PM
Richard Hipp
> 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...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
Marc L. Allen
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-use...@sqlite.org [mailto:sqlite-users-
> bou...@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?
>
Steinar Midtskogen
> 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.
Sounds like you need to convert these timestamps into unix time
(seconds since Jan 1 1970), calculate the average, then convert it
back to its original format.
--
Steinar
C M
<mla...@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.)
Marc L. Allen
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-use...@sqlite.org [mailto:sqlite-users-
> bou...@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?
>
Luuk
> [C M <cmpy...@gmail.com>]
>
>> 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.
>
> Sounds like you need to convert these timestamps into unix time
> (seconds since Jan 1 1970), calculate the average, then convert it
> back to its original format.
>
yes, but dont include the dat info... ;)
Below is converted it to time since '07:00:00'
select * from tijd;
+---------------------+
| t |
+---------------------+
| 2012-02-26 23:00:00 |
| 2012-02-26 01:00:00 |
| 2012-02-25 23:30:00 |
+---------------------+
select
ADDTIME('07:00:00',TIME(AVG( CASE WHEN time(t)<'07:00:00' THEN
addtime('24:00:00',time(t)) ELSE time(t) END))) AVERAGE
from tijd;
+-----------------+
| AVERAGE |
+-----------------+
| 22:43:41.666666 |
+-----------------+
--
Luuk
Luuk
oops, that was MySQL, and this is about sqlite... ;)
Luuk
OK, i give up.....
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tijd(t int(11));
INSERT INTO "tijd" VALUES('2012-02-25 23:00:00');
INSERT INTO "tijd" VALUES('2012-02-25 01:00:00');
INSERT INTO "tijd" VALUES('2012-02-25 23:30:00');
COMMIT;
sqlite> select avg(t), round((avg(t)-0.5)/60),
avg(t)-60*round((avg(t)-0.5)/60) from (
...> select strftime('%H',t)*60+strftime('%M',t) t from tijd where
strftime('%H',t)*60+strftime('%M',t)>420
...> union
...> select (strftime('%H',t)+24)*60+strftime('%M',t) t from tijd
where strftime('%H',t)*60+strftime('%M',t)<=420
...> )
...> ;
avg(t) round((avg(t)-0.5)/60) avg(t)-60*round((avg(t)-0.5)/60)
---------- ---------------------- --------------------------------
1430.0 24.0 -10.0
sqlite>
there's probably a correct way to do this too ;)
Black, Michael (IS)
Time from noon to noon becomes midnight to midnight. Then you just add the 12 hours back in.
CREATE TABLE tijd(t int(11));
INSERT INTO "tijd" VALUES('2012-02-25 22:00:00');
INSERT INTO "tijd" VALUES('2012-02-27 01:00:00');
INSERT INTO "tijd" VALUES('2012-02-27 23:00:00');
sqlite> select time(avg(time(t,'-12 hour')+12)*3600,'unixepoch') from tijd;
23:20:00
You may pick an offset other than 12 depending on your data.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-use...@sqlite.org [sqlite-use...@sqlite.org] on behalf of C M [cmpy...@gmail.com]
Sent: Friday, February 24, 2012 2:51 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] SELECT average timestamp to get average time of day?
C M
<mla...@outsitenetworks.com>wrote:
> 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?
>
It's for individuals, and it is possible for individuals to shift or drift
by any amount.
>
> 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.
>
Yes, this is a good idea, the same as was given in another response.
Thanks.
I found that this type of measure is referred to as the "mean of circular
quantities", and there is even a Wikipedia page about that...I had just
never thought about it before. I also found the Mitsuta Method for dealing
with this type of issue. But in any approach, things break down if data is
strewn all over a 24 hour period.
> There are cases where this will fail, but you might be able to detect data
> sets that will cause this issue and ignore them.
>
I will have to just come up with a reasonable check of the data's variance
and if I find it is all over the clockface, let the user know that the mean
bedtime can't really be computed due to the erratic data. Maybe if only a
few outliers are found I could filter them out. I may post a follow-up
question regarding that.
Thanks!
C M
> wrote:
> I subtract 12 hours...so any time from 24:00:00 to 12:00:00 will work.
>
> Time from noon to noon becomes midnight to midnight. Then you just add
> the 12 hours back in.
>
>
>
> CREATE TABLE tijd(t int(11));
> INSERT INTO "tijd" VALUES('2012-02-25 22:00:00');
> INSERT INTO "tijd" VALUES('2012-02-27 01:00:00');
> INSERT INTO "tijd" VALUES('2012-02-27 23:00:00');
>
>
>
> sqlite> select time(avg(time(t,'-12 hour')+12)*3600,'unixepoch') from
> tijd;
> 23:20:00
>
>
>
> You may pick an offset other than 12 depending on your data.
>
Thank you, this should work well for me, and it is good to see how one
should write it as an SQLite query.