Thread: time data type question

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

Sbob <sbob@quadratum-braccas.com> 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