time data type question
От | Sbob |
---|---|
Тема | time data type question |
Дата | |
Msg-id | b28e1928-7822-46aa-a0c8-ad4e985d721f@quadratum-braccas.com обсуждение исходный текст |
Ответы |
Re: time data type question
Re: time data type question |
Список | pgsql-admin |
All; I am testing a planned change for a table in our db - PostgreSQL 14 I created a table like this: create table alter_test4 (id int, active_ts timestamp without time zone, active_time time without time zone); I set my current timezone is set to Central Time: postgres=# set timezone = 'US/Central'; SET Then I inserted some rows: postgres=# insert into alter_test4 VALUES (1, now() - interval '14 days' , now() - interval '1 hours'); INSERT 0 1 postgres=# insert into alter_test4 VALUES (2, now() - interval '4 days' , now() - interval '7 hours'); INSERT 0 1 postgres=# insert into alter_test4 VALUES (3, now() - interval '1 day' , now() - interval '4 hours'); INSERT 0 1 postgres=# select * from alter_test4; id | active_ts | active_time ----+----------------------------+----------------- 1 | 2024-11-22 13:24:20.675575 | 12:24:20.675575 2 | 2024-12-02 13:24:29.136082 | 06:24:29.136082 3 | 2024-12-05 13:24:40.346881 | 09:24:40.346881 (3 rows) Then I altered both the active_ts and the active_time column data types to include time zone postgres=# ALTER TABLE alter_test4 alter column active_ts set data type timestamp with time zone; ALTER TABLE postgres=# ALTER TABLE alter_test4 alter column active_time set data type time with time zone; ALTER TABLE Now a select shows the timezone offset: postgres=# select * from alter_test4; id | active_ts | active_time ----+-------------------------------+-------------------- 1 | 2024-11-22 13:24:20.675575-06 | 12:24:20.675575-06 2 | 2024-12-02 13:24:29.136082-06 | 06:24:29.136082-06 3 | 2024-12-05 13:24:40.346881-06 | 09:24:40.346881-06 (3 rows) However, if I change my timezone and re-run the select only the timestamp columns reflect the new timezone, the time columns remain the same: postgres=# set timezone = 'America/Denver'; SET postgres=# select * from alter_test4; id | active_ts | active_time ----+-------------------------------+-------------------- 1 | 2024-11-22 12:24:20.675575-07 | 12:24:20.675575-06 2 | 2024-12-02 12:24:29.136082-07 | 06:24:29.136082-06 3 | 2024-12-05 12:24:40.346881-07 | 09:24:40.346881-06 (3 rows) I thought I would see the time columns shift to mountain time as well. am I doing something wrong? Thanks in advance
В списке pgsql-admin по дате отправления: