Below are two functions that can be used to return the minutes 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 minutes portion.

Here’s an example:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2035-01-01 06:45:30')
FROM DUAL;

Result:

45

It’s the MINUTE keyword that extracts the minute part from the datetime value. We can get other date parts by changing it to the relevant keyword. For example, YEAR, MONTH, HOUR, SECOND, 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 minutes 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 minutes, we can use the MI format element to extract the minutes:

SELECT TO_CHAR(TIMESTAMP '2035-01-01 06:45:30', 'MI')
FROM DUAL;

Result:

45

Alternatively, if we wanted to return the minutes along with the other time components like hours and seconds, we could either add the various format elements, or we could simply use the TS format element:

SELECT TO_CHAR(TIMESTAMP '2035-01-01 06:45:30', 'TS')
FROM DUAL;

Result:

6:45:30 AM