Date: Mon, 28 Apr 2025 23:09:38 -0400
From: Bruce Momjian <bruce@momjian.us>
To: H Witt <i5d@live.com>
Cc: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Subject: Re: why at time zone tz_offset not equal tz_name?  tz_name has same
 offset in pg_timezone_names
Message-ID: <aBBC8vE-nlF79BFk@momjian.us>
References: 
 <OSBPR01MB299716C895A69A6DFCF3357C9B812@OSBPR01MB2997.jpnprd01.prod.outlook.com>
 <OSBPR01MB29975FFDA1D860B20A8AC85C9B802@OSBPR01MB2997.jpnprd01.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <OSBPR01MB29975FFDA1D860B20A8AC85C9B802@OSBPR01MB2997.jpnprd01.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aBBC8vE-nlF79BFk%40momjian.us>
Precedence: bulk

On Tue, Apr 29, 2025 at 02:40:45AM +0000, H Witt wrote:
> select 
> '2025-02-03 15:04:05'::timestamptz at time zone '+08:00',
> '2025-02-03 15:04:05'::timestamptz at time zone 'Asia/Shanghai'
> 
> display
> |2025-02-02 23:04:05.000|2025-02-03 15:04:05.000|

The sign is wrong in the first column:

	SELECT
	'2025-02-03 15:04:05'::timestamptz at time zone '-08:00',
	'2025-02-03 15:04:05'::timestamptz at time zone 'Asia/Shanghai'
	;
	      timezone       |      timezone
	---------------------+---------------------
	 2025-02-04 04:04:05 | 2025-02-04 04:04:05

See:

	https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

	The offset fields specify the hours, and optionally minutes and
	seconds, difference from UTC. They have the format hh[:mm[:ss]]
	optionally with a leading sign (+ or -). The positive sign is used
	for zones west of Greenwich. (Note that this is the opposite of
	the ISO-8601 sign convention used elsewhere in PostgreSQL.) hh
	can have one or two digits; mm and ss (if used) must have two.

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

  Do not let urgent matters crowd out time for investment in the future.