Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJfr6-004WCf-Qr for pgsql-admin@arkaria.postgresql.org; Fri, 06 Dec 2024 21:27:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tJfr4-00DvZo-65 for pgsql-admin@arkaria.postgresql.org; Fri, 06 Dec 2024 21:27:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJfr3-00DvZB-Gi for pgsql-admin@lists.postgresql.org; Fri, 06 Dec 2024 21:27:18 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJfqx-001PCm-AZ for pgsql-admin@lists.postgresql.org; Fri, 06 Dec 2024 21:27:17 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 5CB14380C45 for ; Fri, 6 Dec 2024 22:27:08 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id wYloEdUbhfvL for ; Fri, 6 Dec 2024 22:27:08 +0100 (CET) Received: from [127.0.0.1] (xdsl-78-35-178-34.nc.de [78.35.178.34]) by rs.plausibolo.de (Postfix) with ESMTPSA id EA1CF380A70 for ; Fri, 6 Dec 2024 22:27:07 +0100 (CET) Date: Fri, 06 Dec 2024 22:27:08 +0100 From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Subject: Re: time data type question User-Agent: K-9 Mail for Android In-Reply-To: References: Message-ID: <6AC0CEFB-23E7-42FB-A35F-563DB3720611@jakobs.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=----XT90OL24BC7XR6MUZN78UMTQSD9S75 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------XT90OL24BC7XR6MUZN78UMTQSD9S75 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable See here: --=20 Holger Jakobs, Bergisch Gladbach=20 Tel=2E +49 178 9759012 Am 6=2E Dezember 2024 20:32:50 MEZ schrieb Sbob : >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=3D# set timezone =3D 'US/Central'; >SET > > >Then I inserted some rows: >postgres=3D# insert into alter_test4 VALUES (1, now() - interval '14 days= ' , now() - interval '1 hours'); >INSERT 0 1 >postgres=3D# insert into alter_test4 VALUES (2, now() - interval '4 days'= , now() - interval '7 hours'); >INSERT 0 1 >postgres=3D# insert into alter_test4 VALUES (3, now() - interval '1 day' = , now() - interval '4 hours'); >INSERT 0 1 > > >postgres=3D# select * from alter_test4; >=C2=A0id |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 active_ts=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0 active_ti= me >----+----------------------------+----------------- >=C2=A0 1 | 2024-11-22 13:24:20=2E675575 | 12:24:20=2E675575 >=C2=A0 2 | 2024-12-02 13:24:29=2E136082 | 06:24:29=2E136082 >=C2=A0 3 | 2024-12-05 13:24:40=2E346881 | 09:24:40=2E346881 >(3 rows) > > >Then I altered both the active_ts and the active_time column data types= =C2=A0 to include time zone > > >postgres=3D# ALTER TABLE alter_test4 alter column active_ts set data type= timestamp with time zone; >ALTER TABLE >postgres=3D# ALTER TABLE alter_test4 alter column active_time set data ty= pe time with time zone; >ALTER TABLE > > >Now a select shows the timezone offset: > >postgres=3D# select * from alter_test4; >=C2=A0id |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ac= tive_ts=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0= =C2=A0=C2=A0 active_time >----+-------------------------------+-------------------- >=C2=A0 1 | 2024-11-22 13:24:20=2E675575-06 | 12:24:20=2E675575-06 >=C2=A0 2 | 2024-12-02 13:24:29=2E136082-06 | 06:24:29=2E136082-06 >=C2=A0 3 | 2024-12-05 13:24:40=2E346881-06 | 09:24:40=2E346881-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=3D# set timezone =3D 'America/Denver'; >SET >postgres=3D# select * from alter_test4; >=C2=A0id |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ac= tive_ts=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0= =C2=A0=C2=A0 active_time >----+-------------------------------+-------------------- >=C2=A0 1 | 2024-11-22 12:24:20=2E675575-07 | 12:24:20=2E675575-06 >=C2=A0 2 | 2024-12-02 12:24:29=2E136082-07 | 06:24:29=2E136082-06 >=C2=A0 3 | 2024-12-05 12:24:40=2E346881-07 | 09:24:40=2E346881-06 >(3 rows) > > > >I thought I would see the time columns shift to mountain time as well=2E = am I doing something wrong? > > >Thanks in advance > > > > > > > ------XT90OL24BC7XR6MUZN78UMTQSD9S75 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
--
Holger Jakobs, Bergisch= Gladbach
Tel=2E +49 178 9759012


Am 6=2E Dezember 2024 20:32:50 MEZ schrieb Sbob &= lt;sbob@quadratum-braccas=2Ecom>:
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 wit=
hout time zone, active_time time without time zone);

I set my curren=
t timezone is set to Central Time:

postgres=3D# set timezone =3D 'US=
/Central';
SET


Then I inserted some rows:
postgres=3D# ins=
ert into alter_test4 VALUES (1, now() - interval '14 days' , now() - interv=
al '1 hours');
INSERT 0 1
postgres=3D# insert into alter_test4 VALUES=
 (2, now() - interval '4 days' , now() - interval '7 hours');
INSERT 0 1=

postgres=3D# insert into alter_test4 VALUES (3, now() - interval '1 day=
' , now() - interval '4 hours');
INSERT 0 1


postgres=3D# sele=
ct * from alter_test4;
 id |      &nb=
sp;  active_ts          |=
   active_time
----+----------------------------+-------------=
----
  1 | 2024-11-22 13:24:20=2E675575 | 12:24:20=2E675575
&nbs=
p; 2 | 2024-12-02 13:24:29=2E136082 | 06:24:29=2E136082
  3 | 2024-=
12-05 13:24:40=2E346881 | 09:24:40=2E346881
(3 rows)


Then I a=
ltered both the active_ts and the active_time column data types  to in=
clude time zone


postgres=3D# ALTER TABLE alter_test4 alter colum=
n active_ts set data type timestamp with time zone;
ALTER TABLE
postg=
res=3D# 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=3D# select * from alter_test4;
 id | &=
nbsp;         active_ts  =
         |    active=
_time
----+-------------------------------+--------------------
 =
; 1 | 2024-11-22 13:24:20=2E675575-06 | 12:24:20=2E675575-06
  2 | =
2024-12-02 13:24:29=2E136082-06 | 06:24:29=2E136082-06
  3 | 2024-1=
2-05 13:24:40=2E346881-06 | 09:24:40=2E346881-06
(3 rows)


How=
ever, if I change my timezone and re-run the select only the timestamp colu=
mns reflect the new timezone, the time columns remain the same:

post=
gres=3D# set timezone =3D 'America/Denver';
SET
postgres=3D# select *=
 from alter_test4;
 id |       &=
nbsp;   active_ts        =
   |    active_time
----+----------------------=
---------+--------------------
  1 | 2024-11-22 12:24:20=2E675575-0=
7 | 12:24:20=2E675575-06
  2 | 2024-12-02 12:24:29=2E136082-07 | 06=
:24:29=2E136082-06
  3 | 2024-12-05 12:24:40=2E346881-07 | 09:24:40=
=2E346881-06
(3 rows)



I thought I would see the time colu=
mns shift to mountain time as well=2E am I doing something wrong?

Thanks in advance







------XT90OL24BC7XR6MUZN78UMTQSD9S75--