Обсуждение: Incorrect/confusing information about timetz

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/datatype-datetime.html
Description:

This statement in section 8.5.3 states
"All timezone-aware dates and times are stored internally in UTC. They are
converted to local time in the zone specified by the TimeZone configuration
parameter before being displayed to the client." 
Is not correct for timetz

On Fri, May 19, 2023 at 06:03:43PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/15/datatype-datetime.html
> Description:
> 
> This statement in section 8.5.3 states
> "All timezone-aware dates and times are stored internally in UTC. They are
> converted to local time in the zone specified by the TimeZone configuration
> parameter before being displayed to the client." 
> Is not correct for timetz

Uh, yes to the storage part, no to the output part.  ;-)  Postgres does
store the timetz time zone offset, but it doesn't adust it once it is
stored so doesn't adjust for the session time zone:

    SHOW timezone;
         TimeZone
    ------------------
     America/New_York
    
    CREATE TABLE test (x1 time, x2 timetz, x3 timestamp with time zone);
    
    INSERT INTO test VALUES ('09:00:00', '09:00:00', '2023-09-07 09:00:00');
    
    SELECT * FROM test;
        x1    |     x2      |           x3
    ----------+-------------+------------------------
     09:00:00 | 09:00:00-04 | 2023-09-07 09:00:00-04
    
    SET TIMEZONE = 'Asia/Tokyo';

    SELECT * FROM test;
        x1    |     x2      |           x3
    ----------+-------------+------------------------
     09:00:00 | 09:00:00-04 | 2023-09-07 22:00:00+09
     --         --      ---              --      ---

You can see it stored _internally_ here:

    ./src/include/utils/date.h

    typedef int64 TimeADT;

    typedef struct
    {
        TimeADT     time;           /* all time units other than months and years */
        int32       zone;           /* numeric time zone, in seconds */
    } TimeTzADT;

Do we want to document this?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:
> Uh, yes to the storage part, no to the output part.  ;-)  Postgres does
> store the timetz time zone offset, but it doesn't adust it once it is
> stored so doesn't adjust for the session time zone:

Right, it just stores a numeric UTC offset.

> Do we want to document this?

Section 8.5.1.2. Times already says "The appropriate time zone offset
is recorded in the time with time zone value."  Maybe that could be
made a little more precise, say "The resolved numeric offset from UTC
is recorded in the time with time zone value."

            regards, tom lane

On Thu, Sep  7, 2023 at 04:41:48PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Uh, yes to the storage part, no to the output part.  ;-)  Postgres does
> > store the timetz time zone offset, but it doesn't adust it once it is
> > stored so doesn't adjust for the session time zone:
> 
> Right, it just stores a numeric UTC offset.
> 
> > Do we want to document this?
> 
> Section 8.5.1.2. Times already says "The appropriate time zone offset
> is recorded in the time with time zone value."  Maybe that could be
> made a little more precise, say "The resolved numeric offset from UTC
> is recorded in the time with time zone value."

Yeah, there is no mention of it odd output behavior.  Here is a patch to
add that.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

On Thu, Sep  7, 2023 at 05:00:59PM -0400, Bruce Momjian wrote:
> On Thu, Sep  7, 2023 at 04:41:48PM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > Uh, yes to the storage part, no to the output part.  ;-)  Postgres does
> > > store the timetz time zone offset, but it doesn't adust it once it is
> > > stored so doesn't adjust for the session time zone:
> > 
> > Right, it just stores a numeric UTC offset.
> > 
> > > Do we want to document this?
> > 
> > Section 8.5.1.2. Times already says "The appropriate time zone offset
> > is recorded in the time with time zone value."  Maybe that could be
> > made a little more precise, say "The resolved numeric offset from UTC
> > is recorded in the time with time zone value."
> 
> Yeah, there is no mention of it odd output behavior.  Here is a patch to
> add that.

Patch applied back to Postgres 11.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.