Extracting Time from Timestamp in Redshift

Question

I am using Redshift and am looking to extract the time from the timestamp.

Here is the timestamp: 2017-10-31 23:30:00

and I would just like to get the time as 23:30:00

I tried using cast(the_timestamp_column as time) but ran into this error message:

error message: Function ""time"(timestamp without time zone)" not supported.

Answer 1

Use to_char with an appropriate format mask to extract the time component of your timestamp:

select to_char(the_timestamp_column, 'HH24:MI:SS')
from your_table;

Demo