Date: Thu, 7 Sep 2023 16:33:37 -0400
From: Bruce Momjian <bruce@momjian.us>
To: davecramer@postgres.rocks, pgsql-docs@lists.postgresql.org
Subject: Re: Incorrect/confusing information about timetz
Message-ID: <ZPozoUwxa80aarQp@momjian.us>
References: <168451942371.714.9173574930845904336@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <168451942371.714.9173574930845904336@wrigleys.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/ZPozoUwxa80aarQp%40momjian.us>
Precedence: bulk

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.