CODEWITHSUNDEEP
sqlamazon-redshift
Mewtwo
Mewtwo

Reputation: 1311

Weird error returned by date_trunc() in Redshift

So I am encountering the following weird behavior on a Redshift database.

The following query runs as expected truncating the current timestamp to month:

select date_trunc('month', now()) -- returns 2019-01-01 00:00:00+00

On the contrary this query:

select date_trunc('month', now())
from table

returns the following error

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

There is no INFO message but obviously the problem has to do with the date_trunc() as it is the only function used.

Normally I would expect that it would work as it does on PostgreSQL, returning the current timestamp truncated to month as many time as the number of rows of the table.

I have also taken a look at this in case date_trunc() wasn't fully supported yet but I cannot find any reference.

Is there anything that I am missing? Any workaround?

Upvotes: 3

Views: 1320

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

now() is a leader-node only function. that means it works if you don't use a table.

Instead of now() you can use current_timestamp

e.g.

select date_trunc('month', current_timestamp)
from table;

This will work fine.

see https://docs.aws.amazon.com/redshift/latest/dg/Date_functions_header.html

(right at the bottom)

Upvotes: 6

Related Questions