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?

score 9 · Accepted Answer · 2017-11-02T07:31:33.507

9

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)

edited Nov 02 '17 at 07:31

answered Nov 02 '17 at 07:17

tested as working using select '2017-10-31 23:30:00'::timestamp::time; returns 23:30:00 – Jon Scott Nov 02 '17 at 08:16
1

Would we be able to use this method on an entire column? When I try it, I get this error message: `Function ""time"(timestamp without time zone)" not supported.` Would I have to specify a timezone? – user8659376 Nov 03 '17 at 04:52

score 1 · Answer 2 · answered Nov 02 '17 at 07:27

1

Please go through this link

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');

answered Nov 02 '17 at 07:27

Jawad Siddiqui

This answer does not return 23:30:00 using "2017-10-31 23:30:00" as input – Jon Scott Nov 02 '17 at 08:17
Try select substring(convert(varchar(150),'2016-01-04 00:12:06'),12,8) – Jawad Siddiqui Nov 02 '17 at 09:29
right but - see the correct answer above which is simply "::time" – Jon Scott Nov 02 '17 at 09:53

2 Answers2