Блог

Сервер находится в EST, я сохраняю timestamptz как lastlogin .

Похоже, что преобразование из UTC в EST происходит в обратном направлении.

 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
 

Если для преобразования из UTC в EST я вычитаю 5 часов, почему EST составляет 5 часов в будущем? Это как если бы преобразование в UTC на самом деле давало мне время EST, а преобразование в EST давало мне время UTC.

Но это работает:

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

Редактировать:

Я обнаружил, что выполнение двух преобразований, похоже, работает:

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

В Popsql показано, как преобразовать UTC в местный часовой пояс в PostgreSQL. Получается, что первый at time zone устанавливает часовой пояс для времени, в то время как второй фактически выполняет преобразование?

Я понял это.

Во-первых, очевидно, что временная метка может не быть a timestamptz . DBeaver говорит, что это происходит, когда я просматриваю столбцы таблицы, но сообщает timestamp , если я запрашиваю его. Pgcli также говорит , что это a timestamp .

Тем не менее, я обнаружил в документах postgresql 9.9.3 В TIME ZONE, что:

Оператор AT TIME ZONE преобразует временную метку без часового пояса в / из временной метки с часовым поясом и времени со значениями часового пояса в разные часовые пояса.

Объяснение в правке моего вопроса было правильным. AT TIME ZONE on a timestamp установит часовой пояс времени на то, что вы укажете, затем второй AT TIME ZONE преобразует его.

Что вы видите и почему важно хранить значения временных меток в 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