CODEWITHSUNDEEP
sqlitedatetimetimedifference
xpt
xpt

Reputation: 22984

SQLite time difference calculations in HH:MM:SS format

How to calculate time difference in HH:MM:SS format in SQLite?

SELECT time(strftime('%s','2017-11-01 22:25:28') - strftime('%s','2017-11-01')); gives me:

12:00:00

and

SELECT datetime(strftime('%s','2017-11-01 22:25:28') - strftime('%s','2017-11-01')); gives me:

-4492-12-04 12:00:00

Upvotes: 3

Views: 4260

Answers (1)

CL.
CL.

Reputation: 180030

As documented in the documentation, numbers are interpreted as Julian date numbers by default.

If your value is a number of seconds, you have to interpret it as a number of seconds, i.e., as a Unix timestamp:

SELECT time(strftime('%s','2017-11-01 22:25:28') - strftime('%s','2017-11-01'), 'unixepoch');

Upvotes: 6

Related Questions