CODEWITHSUNDEEP
amazon-redshift
RustyShackleford
RustyShackleford

Reputation: 3677

Are there limitation on converting or casting timestamp column to time in redshift database without timezone?

I am working with Amazon Redshift database using Postgresql. I have a timestamp column which I need to convert over to just time. I have used the following line to determine column properties:

select * from pg_table_def where tablename = 'tableA'

This line gives the detail that the timestamp column is type 'timestamp without time zone'.

To test casting method in Postgresql i tried the following line:

select '2016-10-01 12:12:12'::time

which casts the timestamp to '12:12:12'

I want to recreate this for the entire column 'timestamp' in my table, but run into the following error, when I run the following line: 

select timestamp::time from tableA

'SQL Error [500310] [0A000]: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

Is there a limitation on timestamp columns with NO timezone to convert them into just time?

What am I doing wrong in which each record of the column is not converting to time?

--edit-- @halil here is the modified line I have been using from yours. 

select (extract(hour from timestamp) || ':' || extract(minute from timestamp) || ':' || extract(second from timestamp)) as my_time from tableA

--edit-- @halil here is the lines used to subtract timestamp. All I doing is subtracting the next timestamp from the previous one:

select eventtimestamp - lead(eventtimestamp) over (order by eventtimestamp)
from ga_visit_events

Upvotes: 0

Views: 250

Answers (1)

halil
halil

Reputation: 1812

I checked redshift documentation and could not find a proper fucntion to do this. But you can use this query to get what you want

select date_part(h, your_tm)||':'||date_part(min, your_tm)||':'||date_part(sec, your_tm) from your_table

Upvotes: 0

Related Questions