CODEWITHSUNDEEP
timedb2
user935618
user935618

Reputation: 65

DB2 seconds of day to readable time

I'm reading an integer field (store open time) that's in seconds of a day, and converting it to something someone would actually be able to read.

Example: 32400 seconds = 9 AM (32400/3600 = 9)

This is fine if every store opens exactly on the hour, but now some stores open and close at the half hour.

Example 9:30 comes back as 9 (34200/3600 = 9), which is not good. I don't want 34200 to come back as 9.5 either.

Ideally I want 32400 to come back as 900 and 34200 to come back as 930, and 46800 to come back as 1300. Both the input and output parameters need to be integers (I can't change column types).

Any easy way to do this in DB2? Thanks

Upvotes: 1

Views: 1993

Answers (2)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

This is very simple (provided you're using DB2 on Linux, UNIX or Windows):

VALUES INT(TIME('00:00:00') + 34200 seconds)/100

This will return the desired integer 930 for an input of 34200, and 1300 for an input of 46800.

Upvotes: 4

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

I'm assuming that your current data is showing seconds, and not milliseconds as initially stated...

The following will get you a valid time data type in DB2:

SELECT CAST('00:00:00' as TIME) + (34200/3600) HOURS + MOD(34200/60, 60) MINUTES
FROM sysibm/sysdummy1

After this, translation to a better-readable format is easily handled in your display code.

Upvotes: 1

Related Questions