Below are two functions that can be used to return the seconds portion from a datetime value in Oracle Database.

The EXTRACT() Function

The EXTRACT(datetime) function is used for extracting various datetime parts from a datetime value. This includes the seconds portion.

Here’s an example:

SELECT 
    EXTRACT(SECOND FROM TIMESTAMP '2035-01-01 10:15:37')
FROM DUAL;

Result:

37

It’s the SECOND keyword that extracts the seconds part from the datetime value. This will include any fractional seconds:

SELECT 
    EXTRACT(
        SECOND FROM TIMESTAMP '2035-01-01 10:15:37.123456789'
        )
FROM DUAL;

Result:

37.123456789

We can get other date parts by changing it to the relevant keyword. For example, YEAR, MONTH, HOUR, MINUTE, etc.

We can also use the function with other datetime values, such as DATE.

The TO_CHAR(datetime) Function

We can also use the TO_CHAR(datetime) function as an alternative method to get the seconds component from a datetime value.

This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.

The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.

If we only want to return the seconds, we can use one of the format elements that return that component.

There are a few different format elements that we can use when returning the seconds component from a datetime value.

The most obvious one is the SS format element:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SS')
FROM DUAL;

Result:

37

As we can see, the SS format element returns just the seconds part, but not the fractional seconds.

Fractional Seconds

If we want to include the fractional seconds, we can use the FF format element. We can also use X to specify the radix character (the symbol used to separate the integer part from the fractional part):

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SSXFF')
FROM DUAL;

Result:

37.123456789

It’s also possible to limit the precision by appending a number to the FF part:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SSXFF5')
FROM DUAL;

Result:

37.12345

The number can be 1 through 9.

While you could explicitly provide your own radix character – for example, a full stop (.), the X format element can be useful for porting across different languages/territories.

When I ran the above examples, my NLS_TERRITORY parameter was set to AUSTRALIA, which resulted in the radix character being a full stop.

Here’s what happens when I change my NLS_TERRITORY parameter to GERMANY:

ALTER SESSION SET NLS_TERRITORY = 'GERMANY';

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SSXFF')
FROM DUAL;

Result:

37,123456789

Now the radix character is a comma.

Seconds Past Midnight

We can use SSSSS to return the seconds past midnight:

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

Result:

00065