samedi 30 mai 2015

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.

Aucun commentaire:

Enregistrer un commentaire