public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sbob <[email protected]>
To: [email protected] <[email protected]>
Subject: time data type question
Date: Fri, 6 Dec 2024 12:32:50 -0700
Message-ID: <[email protected]> (raw)
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
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: time data type question
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox