On 25-02-2012 11:30, Luuk wrote:
> On 25-02-2012 11:16, Luuk wrote:
>> On 24-02-2012 22:26, Steinar Midtskogen wrote:
>>> [C M <cmpyt...@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 |
>> +-----------------+
>>
>
> oops, that was MySQL, and this is about sqlite... ;)
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 ;)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
