CODEWITHSUNDEEP
sqlite
user763539
user763539

Reputation: 3699

sqlite : time difference between two dates in decimals

I have two two timestamp fields (START,END) and a TIME_DIFF field which is of Integer type. I am trying to calculate the time between START and END field.. I created a trigger to do that :

CREATE TRIGGER [TIME_DIFF]
AFTER UPDATE OF [END]
ON [KLOG]
BEGIN
update klog set TIME_DIFF =
cast(
      (      
        strftime('%s',KLOG.END) -        
 strftime('%s',KLOG.START) 
) as INT
) / 60/60;
END

This gives me result in whole hours.Anything between 0 and 59 minutes is neglected.

I am wondering how can I modify this trigger so it displays in decimals?

Meaning, if the time difference is 1 hour 59 minutes the result would display 1.59.If the time difference is 35 minutes it would display 0.35.

Upvotes: 0

Views: 1727

Answers (1)

CL.
CL.

Reputation: 180030

To interpret a number of seconds as a timestamp, use the unixepoch modifier. Then you can simply use strftime() to format the value:

strftime('%H:%S',
         strftime('%s',KLOG.END) - strftime('%s',KLOG.START),
         'unixepoch')

If you use Julian days instead of seconds, you do not need a separate modifier:

strftime('%H:%S',
         julianday(KLOG.END) - julianday(KLOG.START))

Upvotes: 1

Related Questions