1

I did a huge mistake because I didn't know how the TIMESTAMPTZ datatype works in postgresql.

We need to store sensor data from a handful of different sources.

We have a few tables with two timestamp columns, defined as follows.

CREATE TABLE discriminator0 (
    ...
    timestamp TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT (NOW() AT TIME ZONE 'CEST')
)

The sensors send their data without a timezone (it's cest). In order to match the created_at column with the timestamp column, I set its default value to the current time at the cest timezone.

It didn't occur to me that this is severely flawed until I tried to query the database from a client.

SELECT created_at, timestamp, tempoutside FROM d0 WHERE timestamp > '2020-07-20T13:56:00+02:00' LIMIT 9;

          created_at           |         timestamp          |  tempoutside
-------------------------------+----------------------------+---------------
 2020-07-20 11:56:07.591044+00 | 2020-07-20 11:56:00.001+00 |     27.734375
 2020-07-20 11:56:04.386565+00 | 2020-07-20 11:56:00.008+00 | 26.8798828125
 2020-07-20 11:56:08.562664+00 | 2020-07-20 11:56:00.012+00 | 27.3681640625
 2020-07-20 11:56:09.980161+00 | 2020-07-20 11:56:00.015+00 |  30.126953125
 2020-07-20 11:56:08.71351+00  | 2020-07-20 11:56:00.027+00 | 26.0986328125
 2020-07-20 11:56:06.031052+00 | 2020-07-20 11:56:00.03+00  |            25
 2020-07-20 11:56:05.752615+00 | 2020-07-20 11:56:00.033+00 |  27.001953125
 2020-07-20 11:56:07.248163+00 | 2020-07-20 11:56:00.033+00 | 27.6123046875
 2020-07-20 11:55:56.214678+00 | 2020-07-20 11:56:00.034+00 | 27.1728515625

The problem is, now that I'm specifying a timezone in the query, the result gets shifted by two hours because I stored a cest timestamp as a utc timestamp.

  1. Can I define the timestamp column to be treated as cest even though the timestamp inserted doesn't contain a time zone? I can't modify the timestamp before inserting it into the database. Also, I would like to avoid setting the servers time zone, if possible, but i guess it isn't.

I could write a trigger that locks the table, stores the id of the last inserted row somewhere, sets the server tz to cest and changes the default constraint.

Then I could run a simple update that fixes all prior timestamps. Is there anything that could go wrong with that?

3
  • The only solution I can think of is to create a new table where I can dump incoming data temporarily. Set the servers timezone to cest, export all data, define the tables with the correct default value, import all the data again, then update all created_at and timestamp fields. Then I can start inserting data again and import the temp data. However, I would like to avoid setting the servers tz to cest if possible. Jul 20, 2020 at 22:32
  • Ok, i think another way is to write a trigger that gets executed after an insert. The trigger sets the servers timezone to cest, updates all existing data and changes the default value. I probably need to lock the table while the trigger is running. Jul 21, 2020 at 12:18
  • Another idea would be to store the id of the last insertion using a trigger. Set the servers time zone and tables default value. Then run a simple update on the tables with where id < lastId. I think that's what I'm going to try. I think I still need to lock the table while the trigger is executing. Jul 21, 2020 at 12:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.