Re: time data type question

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 <sbob(at)quadratum-braccas(dot)com>:
>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
>
>
>
>
>
>
>