CODEWITHSUNDEEP
postgresqldatetimetimezonetimestamp-with-timezone
Miguel Wang
Miguel Wang

Reputation: 385

PostgreSQL: Add timezone to columns without converting the times

I have a table with several timestamp columns, basically as follows:

         timestamp_utc        |          timestamp
------------------------------------------------------------
'2020-10-28 08:00:00.000'     | '2020-10-28 04:00:00.000'

I know that the first column is in UTC time, while the second is in NY time. I was wondering how I can add the respective timezone. So the result I would like to have it is something like:

         timestamp_utc        |          timestamp
------------------------------------------------------------
'2020-10-28 08:00:00.000Z'     | '2020-10-28 04:00:00.000-04'

I have tried doing things like:

>>> select timestamp_utc AT TIME ZONE 'UTC' from table;

'2020-10-28 04:00:00.000-04' /* wrong */

>>> select timestamp AT TIME ZONE 'EST' from table;

'2020-10-28 05:00:00.000-04' /* wrong, it should be 04:00:00 */

So finally tried this for the NY part:

>>> select timestamp_est AT TIME ZONE 'America/New_York' from table;

'2020-10-28 04:00:00.000-04'

So I think when I add the timezone with the location, it works. However, when I try to do with true timezones it is converting the time in addition to adding the timezone?

Upvotes: 5

Views: 3441

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246073

You cannot do that, since PostgreSQL (counter-intuitively) doesn't store the time zone along with a timestamp with time zone. This data type is internally stored in UTC, and on display it is converted according to the current setting of the timezone parameter.

So there are two ways to store the time zone offset with the timestamp:

  1. Add a new column timezone that contains the time zone as a string (Europe/Vienna) or a number (2).

  2. Store the timestamp as a string. This is usually a bad idea.

Upvotes: 4

Related Questions