Feedback-ID: i374947ac:Fastmail
Message-ID: <b28e1928-7822-46aa-a0c8-ad4e985d721f@quadratum-braccas.com>
Date: Fri, 6 Dec 2024 12:32:50 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: "pgsql-performance@lists.postgresql.org"
 <pgsql-admin@lists.postgresql.org>
Content-Language: en-US
From: Sbob <sbob@quadratum-braccas.com>
Subject: time data type question
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b28e1928-7822-46aa-a0c8-ad4e985d721f%40quadratum-braccas.com>
Precedence: bulk

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