redshift datediff not working when current_timestamp is used but working when getdate() function is used

5
votes

I am querying a timestamp column with datediff from current_timestamp. But it gives error.

DATEDIFF(minute, timestamp_field ,current_timestamp::TIMESTAMP) 
or 
DATEDIFF(minute, timestamp_field ,current_timestamp)
DataType of timestamp_field is "TIMESTAMP DEFAULT '2016-03-29 20:33:33.404256'::timestamp without time zone"

OutPut:

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

Warnings: Function ""timestamp"(timestamp with time zone)" not supported. Function "timestamptz(timestamp with time zone,integer)" not supported. Function "timestamptz(text)" not supported.

But following query is working if I use getdate() function

DATEDIFF(minute, timestamp_field ,getdate()::TIMESTAMP)
1
amazon-redshift
What's the datetype of your timestamp_field? – Jan Zeiseweis
@JanZeiseweis : Thanks for your comment. I updated my question. its TIMESTAMP Datatype – logan
Please do not tag Redshift questions with postgresql - especially if the function you are having problems with does not exist in Postgres – a_horse_with_no_name
Out of curiosity I executed: select datediff(day, getdate(), current_timestamp); and got the following error: function pg_catalog.date_diff("unknown", timestamp without time zone, timestamp with time zone) does not exist So I guess that current_timestamp returns a timestamp with a timezone and getdate() a timestamp without. Unfortunately this doesn't explain your error message. – Jan Zeiseweis

1 Answers

6
votes

I just simply casted the timestamp with timezone to timestamp and it was working.

example:

select datediff(min, CURRENT_TIMESTAMP::timestamp, CURRENT_TIMESTAMP::timestamp);