CODEWITHSUNDEEP
postgresqlamazon-redshift
user3558313
user3558313

Reputation: 41

Amazon Redshift Date Comparison

I am getting an error "Specified types or functions (one per INFO message) not supported on Amazon Redshift tables." and I am unsure as to why and couldn't find any support anywhere else.

I am trying to filter or delete rows where the current date is after a certain date. I've created a very simple example.

Table "tmp" has one column "date" with one row with the value '2016-01-01'.

I want to delete the row, because it is a date that is in the future.

So my query would be:

DELETE FROM "tmp" WHERE TO_DATE((NOW()),'YYYY-MM-DD HH:MI:SS') < "date";

However I get the error:

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

I also tried casting the "date" column to DATE datatype but same error.

I also tried the function "DATE_CMP" to do a BOOLEAN comparison 

SELECT DATE_CMP((TO_DATE(NOW(),'YYYY-MM-DD HH:MI:SS')),"date"::DATE), "date" 
FROM "tmp"

but that produced the same error.

Could someone help me out with why this is? The only thing I can find in Redshift documentation is here but it doesn't seem to really mention anything relevant.

Upvotes: 4

Views: 3930

Answers (1)

user330315
user330315

Reputation:

to_date(now(), ...) makes no sense. now() is already a date there is no need to convert it to one.

The condition "where the current date is after a certain date" can be written as: 

delete from tmp
where current_date > "date";

All date functions are documented here: http://docs.aws.amazon.com/redshift/latest/dg/Date_functions_header.html

date is a horrible name for a column. For one because it is also a keyword, but more importantly it does not document what the column contains. A start date? An end date? A due date? A visit date? An invoice date?

Upvotes: 2

Related Questions