How would you convert secs to HH:MM:SS format in SQLite?

Question

How would you convert secs to HH:MM:SS format in SQLite?

Answer 1

Try this one:

sqlite> SELECT time(10, 'unixepoch');
00:00:10

Answer 2

If your seconds are more than 24 hours, you need to calculate it yourself. For example, with 100123 seconds, rounding down to minutes:

SELECT (100123/3600) || ' hours, ' || (100123%3600/60) ||' minutes.'
27 hours, 48 minutes

The time or strftime functions will obviously convert every 24-hours to another day. So the following shows 3 hours (the time on the next day):

SELECT time(100123, 'unixepoch')
03:48:43

To get the full 27 hours, you can calculate the hours separately, and then use strftime for the minutes and seconds:

SELECT (100123/3600) || ':' || strftime('%M:%S', 100123/86400.0);
27:48:43