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.

Answer 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