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)
DATEDIFF returns the difference between the date parts of two date or time expressions.
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.
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.
I just simply casted the timestamp with timezone to timestamp and it was working.
example:
select datediff(min, CURRENT_TIMESTAMP::timestamp, CURRENT_TIMESTAMP::timestamp);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With