Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)
like image 767
logan Avatar asked Sep 26 '16 12:09

logan


People also ask

How do you find the difference between two dates in redshift?

DATEDIFF returns the difference between the date parts of two date or time expressions.

How do I get the current date in redshift?

GETDATE returns the current date and time in the current session time zone (UTC by default). It returns the start date or time of the current statement, even when it is within a transaction block.

What is redshift timestamp?

The TIME is an Amazon Redshift Timestamps data type that stores time value without a time zone. One can use this Amazon Redshift Timestamps data type to store time values with up to six digits of precision values for fractional seconds.


1 Answers

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

example:

select datediff(min, CURRENT_TIMESTAMP::timestamp, CURRENT_TIMESTAMP::timestamp);
like image 198
takacsot Avatar answered Dec 29 '22 05:12

takacsot