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

Question

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?

Answer 1

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.