PostgreSQL Timesamp Error


(Dawid) #1

In relation to my other post Date filter up to current point in time

I have created simple CURRENT_TIMESTAMP dimension and gave it some timeframes.

When I use YEAR the SQL behind it writes:

DATE_PART('year', <field>) but that will not be valid for timestamp, should be using EXTRACT(YEAR FROM <field>) instead, don’t you think?

Otherwise following error will be returned:

Invalid operation: function pg_catalog.pgdate_part("unknown", timestamp with time zone) does not exist;

same happens with month and hour of day . I believe there needs to be a bit more thought into when to choose DATE_PART and whenEXTRACT

This could be specific to Redshift (still looking for more information) but that’s why EXTRACT is usually safer option

I solved it by casting CURRENT_TIMESTAMP::TIMESTAMP explicitly thought I think it’s bonkers

(Izzy) #2

Yeah, this seems to work alright in postgresql (it automatically uses EXTRACT instead) but not in redshift. From your error, it looks like redshifts current_timestamp automatically applies a time zone to the time stamp, and the SQL generator is not expecting that.

To avoid doing the casting, you can use GETDATE() which will return a timestamp without timezone.

I’ll check in on if this is intended behavior or not!

(Dawid) #3

I just casted it to TIMESTAMP though of course GETDATE() is another way. It’s not technically a bug but without knowing subtle differences between PostgreSQL and Redshift people might get confused : )