Postgres timestamp conversion is backwards

Question

The server is in EST, I'm storing a timestamptz as lastlogin.

It seems like the conversion from UTC to EST is happening backwards.

select lastlogin at time zone 'EST' as lastlogin from users where id = 1;
-- > 2021-01-13 18:56:28

select lastlogin at time zone 'UTC' as lastlogin from users where id = 1;
-- > 2021-01-13 13:56:28

If to convert from UTC to EST I subtract 5 hours, why is EST 5 hours in the future? It's as if converting to UTC actually gives me the EST time, and converting to EST gives me the UTC time.

But this works:

select NOW() at time zone 'EST', NOW() at time zone 'UTC';
-- > 2021-01-13 14:23:21 2021-01-13 19:23:21

EDIT:

I found out that doing two conversions seems to work:

select lastlogin at time zone 'UTC' at time zone 'EST' as lastlogin from users where id = 1;
-- > 2021-01-13 13:56:28

This is shown on Popsql How to Convert UTC to Local Time Zone in PostgreSQL. Is it that the first at time zone will set the time zone for the time, while the second will actually do the conversion?

What is value of select lastlogin from users where id = 1;? — Adrian Klaver
– Adrian Klaver, Commented Jan 13, 2021 at 19:42
Glad you found an answer, but a word of caution. Don't use EST for the timezone; it is a time zone abbreviation. Instead use the full timezone name; either US/Eastern or America/New York, or other with offset -05:00 - there are 34*** of them). This is because the full name automatically adjusts for daylight savings while the abbreviation does not. *** select * from pg_timezone_names where abbrev = 'EST'; — Belayer
– Belayer, Commented Jan 13, 2021 at 23:29

Dylan Landry · Accepted Answer · 2021-01-13 20:07:19Z

2

I figured it out.

First off, apparently that timestamp may not be a timestamptz. DBeaver says it is when I look at the table columns, but reports timestamp if I query it. Pgcli also says it is a timestamp.

That said, I found in the postgresql docs 9.9.3 AT TIME ZONE that:

The AT TIME ZONE operator converts time stamp without time zone to/from time stamp with time zone, and time with time zone values to different time zones.

The explanation in my question's edit was correct. AT TIME ZONE on a timestamp will set the time's time zone to whatever you specify, then the second AT TIME ZONE will convert it.

answered Jan 13, 2021 at 20:07

Dylan Landry

1,2601 gold badge12 silver badges33 bronze badges

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

3 Comments

Adrian Klaver Over a year ago

In psql what does \d user show for lastlogin. I'm guessing it is timestamptz.

Dylan Landry Over a year ago

Well, I've noticed across two of our dbs lastlogin is different, one is timestamptz and the other timestamp. That's where I was getting confused. The ones in this example were timestamp.

Adrian Klaver Over a year ago

See my answer for further explanation. If nothing else you found out why timestamptz is better option then timestamp.

Adrian Klaver · Accepted Answer · 2021-01-14 17:00:57Z

What you are seeing and why it is important to store timestamp values in timestamptz:

set timezone = 'US/Eastern';

test(5432)=> \d ts_test 
                         Table "public.ts_test"
  Column  |            Type             | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 ts_tz    | timestamp with time zone    |           |          | 
 ts       | timestamp without time zone |           |          | 
 ts_txt   | character varying           |           |          | 
 time_fld | time without time zone      |           |          | 

-- A timestamptz field always stores the value as UTC. A timestamp field is stored -- as a naive value. In below the timestamptz value is rotated to EST as that what -- the timezone is set to. The timestamp is also displayed in EST but with no time -- zone value, so naive.

select ts_tz, ts from ts_test ;
             ts_tz              |             ts             
--------------------------------+----------------------------
 01/14/2021 11:37:13.217229 EST | 01/14/2021 11:37:13.217229

-- Here the time zone is being explicitly set and both values are the same as 
-- timezone = 'EST'. The formally naive value does pick up a time zone designation -- per the docs "timestamp without time zone AT TIME ZONE zone Return Type --timestamp with time -- zone"

select ts_tz AT TIME ZONE 'EST', ts AT TIME ZONE 'EST' from ts_test;
          timezone          |            timezone            
----------------------------+--------------------------------
 01/14/2021 11:37:13.217229 | 01/14/2021 11:37:13.217229 EST

-- Here the the timestamptz value correctly gets rotated to UTC. The timestamp 
-- value gets set to UTC then gets rotated back to EST

 select ts_tz AT TIME ZONE 'UTC', ts AT TIME ZONE 'UTC' from ts_test;
          timezone          |            timezone            
----------------------------+--------------------------------
 01/14/2021 16:37:13.217229 | 01/14/2021 06:37:13.217229 EST

Collectives™ on Stack Overflow

Postgres timestamp conversion is backwards

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related