Re: Incorrect/confusing information about 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.