Show time diff as HH:MM:SS between two datetimes in SQLite

user_2312615 0
Viewed 132

I got two different datetimes: 2020-05-18 12:30:01 and 2020-05-17 13:00:00.
I want to show the time difference between them in the format HH:MM:SS, which is 23:30:01.

If the difference is higher than 24 hours, let's say 28 hours, 12 minutes and 45 seconds, it would show like 28:12:45.

How can I do that in SQLite?

datetime sqlite
2 Answers

SQLite supports a limited number of functions for datetime manipulation.
One of these functions is strftime(), and 

strftime('%s', somedate)

returns the number of seconds from '1970-01-01' up to somedate.

With the use of this function, arithmetic calculations, string padding and concatenations you can get what you want like this:

  CASE WHEN ((strftime('%s', date1) - strftime('%s', date2)) / 3600) < 10 THEN '0' ELSE '' END ||
  ((strftime('%s', date1) - strftime('%s', date2)) / 3600) || ':' ||   
  SUBSTR('0' || (((strftime('%s', date1) - strftime('%s', date2)) / 60) % 60), -2) || ':' ||   
  SUBSTR('0' || ((strftime('%s', date1) - strftime('%s', date2)) % 60), -2)

Replace date1 and date2 with your dates.

See a simplified demo.

user_104988280 
SELECT time(
(
  julianday('2020-05-18 12:30:01')-
  julianday('2020-05-17 13:00:00')
)*60*60*24, 'unixepoch'
);

answsers the question when time difference is lower than 24h...

user_2182230
Related