CODEWITHSUNDEEP
datetimesqlitestring-formatting
vfclists
vfclists

Reputation: 20201

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

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

Upvotes: 13

Views: 12972

Answers (3)

mivk
mivk

Reputation: 14824

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

Upvotes: 12

newtover
newtover

Reputation: 32094

Try this one:

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

Upvotes: 22

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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)

Upvotes: 0

Related Questions