Postgres date_trunc function with timezone

Question

Im using a C# query that automatically uses the date_trunc with timezone function, however when trying to run the function in postgres it doesnt work. I receive this error:

ERROR:  function date_trunc(unknown, timestamp with time zone, unknown) does not exist

According to the Postgres documentation. It should support:

date_trunc(field, source [, time_zone ])

For example I can call,

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');

But not,

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');

Is this optional time_zone field new and my postgres out of date? Currently running version PostgreSQL 11.14.

cogitoergosum · Accepted Answer · 2022-10-31 16:47:06Z

1

Specifying the time zone in date_trunc is not supported in Postgresql 11.

Take a look at AT TIME ZONE described just below date_trunc in the link above, you could use something like

date_trunc('day', TIMESTAMP '2001-02-16 20:38:40+00' AT TIME ZONE 'Australia/Sydney')

HTH

answered Oct 31, 2022 at 16:47

cogitoergosum

6916 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheProgrammer Over a year ago

That would make sense on why im receiving this error, its weird that my code is generating a query that my current postgres version doesnt support.

Collectives™ on Stack Overflow

Postgres date_trunc function with timezone

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related