Extracting Time from Timestamp in SQL

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

Is that possible?

Answer 1

In Redshift you can simply cast the value to a time:

the_timestamp_column::time

alternatively you can use the standard cast() operator:

cast(the_timestamp_column as time)

Answer 2

Please go through this link

http://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

timezone, timezone_hour, timezone_minute

Supported by the DATE_TRUNC function and the EXTRACT for time stamp with time zone (TIMESTAMPTZ)

Examples is here

select extract(minute from timestamp '2009-09-09 12:08:43');
select extract(hours from timestamp '2009-09-09 12:08:43');