Igor Tandetnik wrote:
> 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;
>
You can use the string manipulation functions to reformat his time
string as one that is acceptable to the date and time functions and then
use that to update his log as a one time change. This will change all
the timestamps to ISO format. Of course this could be done using another
column in the table, or to another table altogether if you don't want to
change the log table format.
update Log
set timestamp =
substr(timestamp, 1, 4) || '-' ||
substr(timestamp, 5, 2) || '-' ||
substr(timestamp, 7, 2) || ' ' ||
substr(timestamp, 9, 2) || ':' ||
substr(timestamp, 11, 2) || ':' ||
substr(timestamp, 13, 2);
After that you could convert the timestamps directly to julian day numbers.
update Log
set timestamp = julianday(timestamp, 'localtime');
Then Igor's query simplifies to:
select code, strftime('%M:%S', sum(
(select l2.timestamp from Log l2
where l2.timestamp > l1.timestamp
order by timestamp limit 1
)
- timestamp))
from Log l1
group by code;
Which avoids the repeated format conversions. If you want the results as
a simple count of seconds in that state, the outer format conversion can
also be eliminated to give:
select code, sum(
(select l2.timestamp from Log l2
where l2.timestamp > l1.timestamp
order by timestamp limit 1
)
- timestamp)
from Log l1
group by code;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
