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 |
+-----------------+
--
Luuk
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
