Question 253904 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Store original time zone with time stamp

I have a table definition like the following:

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL
);

I want to be able to store the original time offset given for the value that was inserted in the created column. As I understand it, postgresql converts TIMESTAMP WITH TIME ZONE to UTC internally, and then converts them again on fetch based on the value shown by SHOW TIMEZONE.

Let’s say that we’re adding a time stamp that has offset +0900, and that the offset is of interest. When we’ve added the value 2023-06-30T12:34:56+0900, it is converted to UTC and stored in the database.

When the same record is fetched, the date is converted to the current time zone. If we’re currently at CET +0100, it will display as 2023-06-30T04:34:56+0100.

While this is correct, it’s not very logical, and it assumes that the original time zone was not of interest.

So how could I store the original time offset, besides storing the value as text and casting it to date as required on read? Can I store the original time offset together with the time stamp, and use that? If so I don’t see the right way, because changing the timestamp’s offset with AT TIME ZONE xyz always returns a new value without time zone, which is not really helpful (as described in the docs).

So perhaps I need to:

  1. Add a second column to save the time offset together with the timestamp
  2. On display, convert the timestamp back to the original time offset, and build a valid ISO 8601 string append my original time offset information.

This seems a bit convoluted, and some of the steps aren’t very straight forward. I’m hoping to avoid doing it manually with string operations.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As noted by @Matt Johnson-Pint, offsets and time zones are different things. A time zone like Europe/Berlin has a different time offset during summer time and winter time, and postgres keeps track of this. So my example using different offsets across daylight savings time boundaries requires no further attention, as it is already handled correctly by postgres when we're working in within the same time zone.

    If there is a requirement that involved storing timestamps from completely different time offsets, and the original offset is of interest, the following might be useful.

    It appears that storing both the TIMESTAMP WITH TIME ZONE and a string type storing the original value might be the way to go.

    So the table could look like this:

    CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  created_str VARCHAR NOT NULL
);

    and this provides the behaviour I was looking for. The main drawback is that the timestamp is stored twice.

    postgres=# insert into what_happened (action, created, created_str) VALUES('test us pacific', '2023-09-27 11:26:35.844749-07', '2023-09-27 11:26:35.844749-07');
INSERT 0 1
postgres=# insert into what_happened (action, created, created_str) VALUES('test tokyo', '2023-09-28 02:26:35.844749+09', '2023-09-28 02:26:35.844749+09');
INSERT 0 1
postgres=# insert into what_happened (action, created, created_str) VALUES('test berlin', '2023-09-27 20:27:00.725301+02', '2023-09-27 20:27:00.725301+02');
INSERT 0 1
postgres=# select * from what_happened;
 id |    action       |            created            |          created_str
----+-----------------+-------------------------------+-------------------------------
  1 | test us pacific | 2023-09-27 20:26:35.844749+02 | 2023-09-27 11:26:35.844749-07
  2 | test tokyo      | 2023-09-27 20:26:51.236822+02 | 2023-09-28 02:26:35.844749+09
  3 | test berlin     | 2023-09-27 20:27:00.725301+02 | 2023-09-27 20:27:00.725301+02
(3 rows)

    It would probably make sense to write a trigger that sets created_str from created, rather than supplying the same value twice all over the place. I can't store only created_str, because string sorting won't sort correctly across time offsets. If you don't require an index, then the following query orders the created_str correctly:

    SELECT * FROM what_happened ORDER BY (created_str::TIMESTAMP WITH TIME ZONE);

    Creating an index on (created_str::TIMESTAMP WITH TIME ZONE) wasn't entirely straight forward either, but could probably be resolved with a wrapper function marked as IMMUTABLE.

    Still, the safest bet might be to keep both created and created_str. Index and sort by created, and use created_str for display only, or for further processing when the original time zone is of consequence.


  2. 0

    The only way to preserve the time zone that was entered is to save the time zone string in a separate column. Extracting the time zone is best done on the client side.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search