How to Return the Number of Seconds Past Midnight in Oracle Database

Oracle Database provides the SSSSS format element that enables us to get the number of seconds past midnight from a given datetime value.

Example

Here’s an example to demonstrate:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 00:01:05', 'SSSSS')
FROM DUAL;

Result:

Here, we can see that the time portion is 00:01:05, which means that it is 1 minute and 5 seconds past 12 am.

1 minute and 5 seconds is 65 seconds in total.

Here’s another example:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 23:59:59', 'SSSSS')
FROM DUAL;

Result:

Now we’re at the other end of the extreme.

Actually, we could go a step further and return the fractional seconds as well:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 23:59:59.999999999', 'SSSSSXFF')
FROM DUAL;

Result:

86399.999999999

In this case we used the FF format element to return the fractional seconds, and the X format element to return the radix character. This character can be different, depending on the locale of the current session.

See List of Datetime Format Elements in Oracle for a full list of format elements that can be used to format datetime values.