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

Question

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)

Tragedy

What's the datetype of your timestamp_field? – Fernandina

@JanZeiseweis : Thanks for your comment. I updated my question. its TIMESTAMP Datatype – Tragedy

Please do not tag Redshift questions with postgresql - especially if the function you are having problems with does not exist in Postgres – Manon

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. – Fernandina

Answer 1

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

example:

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

Standush

that's not working for me. any tips? SELECT "reviewedAt", "createdAt", DATEDIFF('hour', "createdAt"::timestamp, "reviewedAt"::timestamp) as hours_approved from "blackship$prod"."Application" error [42883] ERROR: function datediff(unknown, timestamp without time zone, timestamp without time zone) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 36 – Thomasinethomason

i'm getting the same error ... any fixes? – Mesothorium

Answer 2

This worked for me:

datediff('day', ref_date::date, actual_date::date)

Elisa