Oracle SQL: How to display a HH24:MI:SS as "Hours: #, Minutes #, Seconds #"?

Question

Title says it all, I want

19:33:25 

to display as

Hours: 19, Minutes: 33, Seconds: 25

This is the query that I used to get 19:33:25, basically a date converted to string.

TO_CHAR(TO_DATE(24 * 60 * 60 * (a.dateEnd - SYSDATE),'sssss'),'hh24:mi:ss')

Is something like this possible?

Thanks for any input!

Solution 1

You could use the EXTRACT(... from DATE) function, but since you need a fixed string output, you can do the same using TO_CHAR. All fixed parts in the string format can be enclosed in double quotes, which gives us something like -

select TO_CHAR(sysdate, '"Hours: "hh", Minutes: "mi" Seconds: "ss') from dual;

Solution 2

You can EXTRACT single fields:

SELECT
    EXTRACT(HOUR FROM (a.dateEnd - SYSDATE)) AS HOUR,
    EXTRACT(MINUTE FROM (a.dateEnd - SYSDATE)) AS MINUTE,
    EXTRACT(SECOND FROM (a.dateEnd - SYSDATE)) AS SECOND
FROM a
WHERE ...

if you want it in a single column with the text, you can use CONCAT:

SELECT CONCAT('Hours: ' || EXTRACT(HOUR FROM (a.dateEnd - SYSDATE)) ||
              ' Minutes: ' EXTRACT(MINUTE FROM (a.dateEnd - SYSDATE)) ||
              ' Seconds: ' EXTRACT(SECOND FROM (a.dateEnd - SYSDATE))
             ) AS time_string
FROM a
WHERE ...