0

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.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
aggis
  • 608
  • 4
  • 9

2 Answers2

1

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');
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • not quite right yet but good direction - need to cast the result back to a time? – Jon Scott Mar 08 '18 at 07:32
  • As I said in my answer, there is no such data type as TIME in Redshift, so it is not possible to cast anything to "a time". – Nathan Griffiths Mar 10 '18 at 01:25
  • 1
    interesting - i hadn't spotted that - although my sql below (that casts to ::time) does run without error AND you can actually perform calculations on that as a TIME!. But you cannot create a table with a time format. – Jon Scott Mar 10 '18 at 09:54
-1

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.

Jon Scott
  • 4,144
  • 17
  • 29