How to extract `hh:mm:ss` from `yyyy/mm/dd hh:mm:ss`-CodePudding

I have following table timestamp(6) without timezone

2000/01/01 0:00:00
2000/01/01 10:00:00
2000/01/01 04:00:00

I would like to get hh:mm:ss My desired result is like follows

0:00:00
10:00:00
04:00:00

Are there any good ways of doing this?

CodePudding user response：

to_char(col,'HH:MI:SS') if you're only extracting to text. The function gives you a lot of formatting flexibility and truncates/discards everything after seconds, without rounding.
To trim leading zero like in presented desired results, you can add an FM fill mode modifier prefix to desired fields in to_char().
col::time(0) will also keep the time data type allowing you to use adequate functions and operators:

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field.

Set to 0 it will round fractions of a second. It was initially suggested by someone else - best choice in my opinion - but the answer was removed when thread got falsely discarded as duplicate.
date_trunc() lets you discard fractions, while keeping an appropriate type.

select now()::timestamp            as "now",
       to_char(now(),'HH:MI:SS')   as "to_char()",
       to_char(now(),'FMHH:MI:SS') as "fillmode", --trims one leading zero
       now()::time(0)              as "::time(0)",--rounds fractions
       date_trunc('seconds',now() )::time  as "date_trunc()";--discards fractions

/*          now             | to_char() | fillmode | ::time(0) | date_trunc()
---------------------------- ----------- ---------- ----------- --------------
 2022-11-14 09:03:04.810214 | 09:03:04  | 9:03:04  | 09:03:05  | 09:03:04
(1 row)
*/