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