Thread: Why timestamptz_pl_interval and timestamptz_mi_interval are not immutable?
Why timestamptz_pl_interval and timestamptz_mi_interval are not immutable?
From
Alexander Pyhalov
Date:
Hi. I'm currently looking on pushing down SQLValue expressions to foreign servers and was surprised that two timestamptz-related functions are not immutable. I see that this was changed in commit commit 1ab415596d1de61561d0de8fe9da4aea207adca4 Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Mon Oct 4 22:13:14 2004 +0000 Correct the volatility labeling of ten timestamp-related functions, per discussion from Friday. initdb not forced in this commit but I intend to do that later. I'm not sure, why timestamptz_pl_interval and timestamptz_mi_interval are not immutable. Even if we change timezone during transaction, addition of the same interval to the same timestamps with time zone gives the same result. postgres=# begin ; BEGIN postgres=*# select current_timestamp; current_timestamp ------------------------------- 2021-08-16 13:26:59.366452+03 (1 row) postgres=*# select timestamptz '2021-08-16 13:26:59.366452+03'; timestamptz ------------------------------- 2021-08-16 13:26:59.366452+03 (1 row) postgres=*# select timestamptz '2021-08-16 13:26:59.366452+03' + '2 days'::interval; ?column? ------------------------------- 2021-08-18 13:26:59.366452+03 (1 row) postgres=*# set timezone to UTC; SET postgres=*# select timestamptz '2021-08-16 13:26:59.366452+03' + '2 days'::interval; ?column? ------------------------------- 2021-08-18 10:26:59.366452+00 (1 row) postgres=*# select timestamptz '2021-08-18 13:26:59.366452+03' = timestamptz '2021-08-18 10:26:59.366452+00'; ?column? ---------- t (1 row) What do I miss? -- Best regards, Alexander Pyhalov, Postgres Professional
RE: Why timestamptz_pl_interval and timestamptz_mi_interval are not immutable?
From
Floris Van Nee
Date:
> > What do I miss? > > -- > Best regards, > Alexander Pyhalov, > Postgres Professional > See for example around DST changes postgres=# begin; BEGIN postgres =# show timezone; TimeZone ------------------ Europe/Amsterdam (1 row) postgres=# select '2021-03-27 15:00 +0100'::timestamptz + interval '1d'; ?column? ------------------------ 2021-03-28 15:00:00+02 (1 row) postgres =# set timezone to UTC; SET postgres =# select '2021-03-27 15:00 +0100'::timestamptz + interval '1d'; ?column? ------------------------ 2021-03-28 14:00:00+00 (1 row) postgres =# select '2021-03-28 15:00:00+02' = '2021-03-28 14:00:00+00'; ?column? ---------- f (1 row)