Weird error returned by date_trunc() in Redshift

Question

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?

Can you try also selecting one or more columns from your actual table? I mean, what would be the point of just selecting a constant for every row of your table, and nothing else? — Tim Biegeleisen
Yes I have and I am still getting the error. What I was actually trying to do was filtering the rows in order to keep only those where date_trunc('month', created_at) = date_trunc('month', now()) but it didn't work so I tried to understand what goes wrong and I ended up with what I explain at the description of the question — Mewtwo

Jon Scott Jon Scott · Accepted Answer · 2019-01-25T10:02:20

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)

Weird error returned by date_trunc() in Redshift

1 Answers