BandIT <[EMAIL PROTECTED]> wrote:
> I have a table:
>
> CREATE TABLE Log
> (
> TimeStamp char(14), -> Format YYYYMMDDHHNNSS
> Code char(5), -> 5 digit status code
> )
>
> example:
>
> 20080314100030 A0001
> 20080314101000 A0002
> 20080314101700 A0000
> 20080314102000 A0002
> 20080314102100 A0000
>
> I would like a query to output the the time between the status codes
> (sum) to know how long each status has been active.
Roughly, you need a query like this:
select code, sum(
(select timestamp from Log l2
where l2.timestamp > l1.timestamp
order by timestamp limit 1
)
- timestamp)
from Log l1
group by code;
The problem is the format of your timestamp, which doesn't lend itself
easily to doing math on it. SQLite provides date and time functions:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
It would be much easier if you could change your timestamps to one of
the formats these functions recognize. Then you can do something like
this:
select code, strftime('%M:%S', sum(
(select julianday(l2.timestamp) from Log l2
where l2.timestamp > l1.timestamp
order by timestamp limit 1
)
- julianday(timestamp)))
from Log l1
group by code;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
