Search code examples
c#postgresqlpgadminpostgres-11

Postgres date_trunc function with timezone

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.

Solution

  • 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