PostgreSQL: Add timezone to columns without converting the times

Question

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?

What are the types for the columns timestamp_utc and timestamp(FYI, I would rename that or did you with timestamp_est)? What is the setting TimeZone for the server? — Adrian Klaver
– Adrian Klaver, Commented Oct 28, 2020 at 15:11
The column names are just dummy examples, so no worries if it is called timestamp. The TimeZone is Est, but I cant change the server time zone. Work around it by using Python instead of PG. As @Laurenz Albe said, probably it cant be done in PG — Miguel Wang
– Miguel Wang, Commented Oct 29, 2020 at 12:25
I would look at this section Time Zones in the docs. It will help you understand what is going on. Hint your TimeZone is not set to EST. Second EST is really just an offset not a full time zone, hence the error in your conversion. Lastly, what data type you are storing your values as, timestamp vs timestamptz is important. I use dateutil in Python all the the time, but it still depends on the underlying behavior in the database. — Adrian Klaver
– Adrian Klaver, Commented Oct 29, 2020 at 14:06

Laurenz Albe · Accepted Answer · 2020-10-28 11:34:43Z

4

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:

Add a new column timezone that contains the time zone as a string (Europe/Vienna) or a number (2).
Store the timestamp as a string. This is usually a bad idea.

answered Oct 28, 2020 at 11:34

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belayer Over a year ago

I would caution against storing the timezone offset (2). As that does NOT take daylight savings (summer time) adjustment into consideration where the appropriate timezone name does.

Laurenz Albe Over a year ago

@Belayer I agree. I just offered the possibility of using a numeric offset since that seems to be what OP asks for.

Collectives™ on Stack Overflow

PostgreSQL: Add timezone to columns without converting the times

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related