Re: Wrong provolatile value for to_timestamp (1 argument) - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Wrong provolatile value for to_timestamp (1 argument)
Date
Msg-id 1905578.1657031599@sss.pgh.pa.us
Whole thread Raw
In response to Re: Wrong provolatile value for to_timestamp (1 argument)  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-hackers
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2022-07-05 at 19:37 +0900, Tatsuo Ishii wrote:
>> Of course the internal representation of timestamp with time zone data
>> type is not affected by the time zone setting. But why other form of
>> to_timestamp is labeled as stable? If your theory is correct, then
>> other form of to_timestamp shouldn't be labeled immutable as well?

> The result of the two-argument form of "to_timestamp" can depend on
> the setting of "lc_time":

It also depends on the session's timezone setting, in a way that
the single-argument form does not.

regression=# show timezone;
     TimeZone     
------------------
 America/New_York
(1 row)

regression=# select to_timestamp(0);
      to_timestamp      
------------------------
 1969-12-31 19:00:00-05
(1 row)

regression=# select to_timestamp('1970-01-01', 'YYYY-MM-DD');
      to_timestamp      
------------------------
 1970-01-01 00:00:00-05
(1 row)

regression=# set timezone = 'utc';
SET
regression=# select to_timestamp(0);
      to_timestamp      
------------------------
 1970-01-01 00:00:00+00
(1 row)

regression=# select to_timestamp('1970-01-01', 'YYYY-MM-DD');
      to_timestamp      
------------------------
 1970-01-01 00:00:00+00
(1 row)

The two results of to_timestamp(0) represent the same UTC
instant, but the other two are different instants.

            regards, tom lane



pgsql-hackers by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Wrong provolatile value for to_timestamp (1 argument)
Next
From: Justin Pryzby
Date:
Subject: Re: Add LZ4 compression in pg_dump