CODEWITHSUNDEEP
sqlite
jerem
jerem

Reputation: 1

Difference between two dates sqlite

I'm trying to calculate the difference between two dates in sqlite. My times are at the format : 15h30

When I try this : 

select time(replace( FIRSTHOUR , 'h' , ':'))-time(replace (SECONDHOUR , 'h' , ':')) 
from table

I just get the result in hour like 2 if my dates are 15h30 and 17h40

How can I get the result in hour and minute like 2:10?

Upvotes: 0

Views: 2419

Answers (1)

Doug Currie
Doug Currie

Reputation: 41180

sqlite> select strftime('%H:%M', 
                         strftime('%s', replace('17h40', 'h' , ':'))
                         - strftime('%s', replace ('15h30', 'h' , ':')),
                        'unixepoch');
02:10
sqlite>

So,

sqlite> select strftime('%H:%M', 
                         strftime('%s', replace(FIRSTHOUR, 'h' , ':'))
                         - strftime('%s', replace (SECONDHOUR, 'h' , ':')),
                        'unixepoch');

Alternatively,

sqlite> select strftime('%H:%M',
                         julianday(replace('17h40', 'h' , ':'))
                         - julianday(replace ('15h30', 'h' , ':'))
                         - 0.5);
02:10
sqlite>

So,

sqlite> select strftime('%H:%M',
                         julianday(replace(FIRSTHOUR, 'h' , ':'))
                         - julianday(replace (SECONDHOUR, 'h' , ':'))
                         - 0.5);

Upvotes: 1

Related Questions