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

How would开发者_运维百科 you convert secs to HH:MM:SS format in SQLite?

Try this one:

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

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

Well, i would do something like this, but i'm getting hours plus 12...

SELECT strftime('%H-%M-%S', CAST(<seconds here> / 86400.0 AS DATETIME)) 

-- For subtracting the 12 hours difference :S
SELECT strftime('%H-%M-%S', CAST(<seconds here> / 86400.0 AS DATETIME) - 0.5)