Amazon redshift - extracting time from timestamp SQL error

0
votes

I am trying to extract the time from a datetime column in my Amazon Redshift database (Postgresql 8.0). I have already referred to previous questions such as this. But I am getting an unusual error.

When I try:

SELECT collected_timestamp::time

or

SELECT cast(collected_timestamp as time)

I get the following error:

ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables

The goal is to pull the time portion from the timestamp such that 2017-11-06 13:03:28 returns 13:03:28.

This seems like an easy problem to solve but for some reason I am missing something. Researching that error does not lead to anything meaningful. Any help is appreciated.

2
sqlamazon-redshift

2 Answers

1
votes

Note that Redshift <> PostgreSQL - it was forked from PostgreSQL but is very different under the hood.

You're trying to cast a timestamp value to a data type of "time" which does not exist in Redshift. To return a value that is only the time component of a timestamp you will need to cast it to a character data type, e.g.:

SELECT to_char(collected_timestamp, 'HH24:MI:SS');
-1
votes

There are a few ways, here is one i use:

SELECT ('2018-03-07 21:55:12'::timestamp - trunc('2018-03-07 21:55:12'::timestamp))::time;

I hope that helps.

EDIT: I have made incorrect use of ::time please see comments on other answer.