Itgenclr010 Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported

Wiki (en)
1

Using an update-statement such as synchronize in combination with PostgreSQL, you may encounter an error such as:

itgenclr010
Cannot write DateTime with Kind=Local to PostgreSQL type ‘timestamp with time zone’, only UTC is supported. Note that it’s not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.

For example, the following statements may trigger this error:

--
-- Make contents of table invalid.
--
update table@postgresql
set    name = 'wrong'
where  id = 1234

create or replace table soll@inmemorystorage
as
select 'ok' name
,      sysdate date_modified
,      1234 id

--
-- Load changes.
--
synchronize table@postgresql
from        soll@inmemorystorage
with        insert 
             or
            update all
            except created_by
            ,      created_at
            ,      date_created
            ignore
            changes
            to     modified_by
            ,      modified_at
            ,      date_modified
            ,      created_by
            ,      created_at
            ,      date_created
identified
by          id

Even worse, the error shown above may not surface depending on the actual execution conditions, but values stay unchanged.

PostgreSQL has recently introduced a large (breaking) change in the handling of date/time values for all the good in the world. However, existing code may not run correctly and require rework.

In this case, the type of the value date_modified is a date/time, but a special one: a date/time expressed in the local timezone. This local timezone is by definition the meaning of sysdate.

PostgreSQL prefers a matching date/time type of UTC in this setup. Replace the use of sysdate by sysdateutc and the problem will have been solved. In the process, the actual value of the the date_modified column is now more correctly when running outside the UTC-timezone. Previously, the offset was incorrect.