0

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.

TheProgrammer
  • 1,314
  • 5
  • 22
  • 44

1 Answers1

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

cogitoergosum
  • 591
  • 4
  • 10
  • 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. – TheProgrammer Oct 31 '22 at 16:52