public inbox for [email protected]help / color / mirror / Atom feed
time data type question 3+ messages / 3 participants [nested] [flat]
* time data type question @ 2024-12-06 19:32 Sbob <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Sbob @ 2024-12-06 19:32 UTC (permalink / raw) To: [email protected] <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: time data type question @ 2024-12-06 19:48 Tom Lane <[email protected]> parent: Sbob <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2024-12-06 19:48 UTC (permalink / raw) To: Sbob <[email protected]>; +Cc: [email protected] Sbob <[email protected]> writes: > I thought I would see the time columns shift to mountain time as well. > am I doing something wrong? timetz acts completely differently from timestamptz. timetz actually stores two fields, a time-of-day (microseconds since midnight I think) and a timezone expressed as numeric offset from UTC. Once stored, the value does not react to changes in the timezone setting. By and large we consider timetz deprecated. It's there because the SQL spec requires it, but it doesn't behave in a way that most people find useful. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: time data type question @ 2024-12-06 21:27 Holger Jakobs <[email protected]> parent: Sbob <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Holger Jakobs @ 2024-12-06 21:27 UTC (permalink / raw) To: [email protected] See here: <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIMES; -- Holger Jakobs, Bergisch Gladbach Tel. +49 178 9759012 Am 6. Dezember 2024 20:32:50 MEZ schrieb Sbob <[email protected]>: >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 > > > > > > > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-06 21:27 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-06 19:32 time data type question Sbob <[email protected]> 2024-12-06 19:48 ` Tom Lane <[email protected]> 2024-12-06 21:27 ` Holger Jakobs <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox