Subject: time data type question

From: Sbob <[email-masked]>
Date: Fri, 6 Dec 2024 12:32:50 -0700

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 06 Dec 2024 14:48:42 -0500

> 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

---

From: Holger Jakobs <[email-masked]>
Date: Fri, 06 Dec 2024 22:27:08 +0100

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-masked]>:
>create table alter_test4 (id int, active_ts timestamp without time zone, active_time time without time zone);
>
>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
>
>
>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
>
>
>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:
>
>I thought I would see the time columns shift to mountain time as well. am I doing something wrong?
>
>
>Thanks in advance
>
>
>
>
>
>
>