Change data time zone value

Question

I need to import data without time zone information in it (however, I know the specific time zone for the data I want to import), but I need the timestamp with time zone format in the database.As soon as I import it and set the timestamp with time zone data type to timestamp with time zone , Postgres will automatically assume that the data in the table is taken from my time zone and assigns me its time zone. Unfortunately, the data I want to import is not from my time interval, so this does not work.

The database also contains data with different time zones. However, the time zone inside the same table is always the same.

Now I can set the database time zone for the time zone of the data I want to import before importing the data (using the SET time zone command) and change it back to my time zone after the import is complete, and I'm sure that the data that has already been saved will not be affect the change in the time zone of the database. But this seems like a rather dirty approach and may cause problems later.

I wonder if there is a more elegant way to specify the time zone for import without the presence of time zone data in the data itself?

In addition, I did not find a way to edit the time zone information after importing. Is there a way not to convert, but simply to edit the time zone for the entire table, assuming that the whole table has the same time zone offset (i.e. if the wrong one was assigned during data input / input)?

Edit:
I managed to specify the time zone when importing, and the whole command:

 set session time zone 'UTC'; COPY tbl FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV; set session time zone 'CET'; 

Data is then imported using the session time zone. I assume this does not affect any other queries in the database at the same time from other connections?

Edit 2:
I found out how to change the time zone of a table:
PostgreSQL Update Time Zone Offset

I believe it is more elegant to change the time zone of a table after import, and then use the session to temporarily change the local time zone. Assuming the whole table has the same time zone, of course.

So the code will now be something close:

 COPY tbl FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV; UPDATE tbl SET <tstz_field> = <tstz_field> AT TIME ZONE '<correct_time_zone>'; 

Answer 1

It is much more efficient to set the time zone for your import session than to update the values ​​later.

I get the impression that you are thinking of a time zone, such as a parameter, that applies to other immutable values ​​in the tables. But this is not at all true. Think of it as an I / O modifier. Actual timestamp values ​​(with or without time zone) are always stored as UTC timestamps inside (number of seconds from '2000-01-01 00:00' ). More details:

• Ignoring Time Zones in General in Rails and PostgreSQL

UPDATE in the second example doubles the size of the table, since each individual row is invalid and a new version has been added (the way UPDATE works with MVCC in Postgres). In addition to the expensive operation, VACUUM will have to work harder later to clear the bloat table. Very inefficient.

safely SET local time zone for the session. This does not affect simultaneous operations. Btw., SET SESSION is the same as regular SET , because SESSION is the default by default.

If you want to be absolutely sure, you can limit the setup to the current transaction using SET LOCAL . I quote the guide here

The effects of SET LOCAL last only until the end of the current transaction, whether it is committed or not. A special case is SET on SET LOCAL within one transaction: the SET LOCAL value will be until the end of the transaction, but then (if the transaction is completed) the SET value will take effect.

Combine together:

 BEGIN; SET LOCAL timezone = 'UTC'; COPY tabledata FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV; COMMIT; 

Check:

 SHOW timezone;