Duplicates at timestamps (CET and CEST)

Support
#1

Dear Easymorphers,

I have a problem with the following situation and hope that you can help me out.

Assume that I have a table in a PostgreSQL-database with table key timestamp WITH TIME zone (just to show the problem). The CREATE-statement is:

CREATE TABLE
“postgres”.“public”.“Summertime”
(
“date” TIMESTAMP WITH TIME zone,
“value” INTEGER,
PRIMARY KEY (“date”)
)
;

Now, I insert two records which are exactly at the point where summertime ends.

INSERT
INTO
“public”.“Summertime”
(
“date”,
“value”
)
VALUES
(
‘2019-10-27T02:05:06+01:00’,
1
);

INSERT
INTO
“public”.“Summertime”
(
“date”,
“value”
)
VALUES
(
‘2019-10-27T02:05:06+02:00’,
2
);

The result is as follows:
grafik

So, I have two records with the same timestamp but different time zones.

Now, I created a little project for demonstration purposes. Just three steps:

  1. Import from database
  2. Database command (Delete all rows)
  3. Export to database

Summertime.morph (2.5 KB)

At the first step, the information about summertime (or not) gets lost.

grafik

When trying to write back the records, I receive a duplicate key error.

Now, my question is: is there a possibility to read the correct timestamp (with the time zone) and - what is even more important- to write back from easymorph with the time zone?

#2

Hi Jochen,

EasyMorph can’t store timezone information in the same cell with a corresponding date. So “Import from database” action just discards timezone information.

But you can use expressions or “Custom SQL” mode in “Import from database” action in order to convert your timestamp with timezone values to UTC timestamps or import timezone offset as a separate column.

As for “Export to database” action - EasyMorph will always export values to datetime\timestamp columns as a timestamp without timezone literals. If you really need to export values in different timezones, it seems that there is no good workaround here.

The one workaround would be to export timestamp values and timezone offsets as two separate columns to a temporary table or temporary columns and the use “Database command” action in “Custom SQL” mode to populate you target column with correct values.

The other workaround would be to export your data to a CSV file and then use “Run program” action to call psql utility to bulk insert CSV data to a database table.

1 Like
#3

Thanks for your quick answer, @andrew.rybka .
In my case, the bulk load will be the best workaround.