Formatting elapsed seconds

10 05 2007

I guess we all have had that awkward moment when you want to know how many hours and minutes 11311 seconds is. I don’t understand why I can’t find such a function in Oracle, seems strange. Today was one of those moments so I created a query that does just that. I leave it to others to create a function from this.

This is what I started with:

SELECT TO_CHAR ( TO_DATE ('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1 / (24 * 3600),'HH24:MI:SS')
FROM DUAL;

A bit verbose so I changed it to:

SELECT TO_CHAR ( TRUNC (SYSDATE, 'DD') + 1 / (24 * 3600),'HH24:MI:SS')
FROM DUAL;

Both give the following result:

|formatted|
-----------
|00:00:01 |

That’s the principle, now for some test data

-- Create a table to hold some test data
CREATE TABLE father_time (seconds NUMBER);
-- Generate seconds as a fraction of a day into the table
-- I personally like the use of the pseudo column LEVEL in this statement

INSERT INTO father_time
WITH x AS
(SELECT (LEVEL - 1) / (24 * 3600)
FROM DUAL
CONNECT BY 1 = 1 AND LEVEL < 24 * 3600 + 3) -- slightly more than a day of seconds
SELECT *
FROM x;

-- Format the elapsed time into hours, minutes and seconds
-- if there are more than 24 hours of seconds the days are 'truncated'

SELECT seconds,
TO_CHAR (TRUNC (SYSDATE, 'DD') + seconds, 'HH24:MI:SS') AS formatted
FROM father_time
ORDER BY seconds DESC;

DROP TABLE father_time;

And here are some interesting parts of the output:

|seconds |formatted|
|-----------|---------|
|1,000011574| 0:00:01|
| 1| 0:00:00|
|0,999988426| 23:59:59|
|0,999976852| 23:59:58|
.
.
|0,99931713 | 23:59:01|
|0,999305556| 23:59:00|
|0,999293981| 23:58:59|
|0,999282407| 23:58:58|
|0,999270833| 23:58:57|
|0,999259259
| 23:58:56|

You can imagine the rest I guess.

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: