Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isSRy-00052t-Bq for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 14:18:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1isSRx-0006OT-3o for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 14:18:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isSRw-0006J4-Rl for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 14:18:12 +0000 Received: from courriel.mines-paristech.fr ([77.158.173.149] helo=antispam-1.ensmp.fr) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1isSRr-0007eG-1c for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 14:18:11 +0000 Received: from smtp-4.mines-paristech.fr (smtp-4.ensmp.fr [77.158.173.143]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by antispam-1.ensmp.fr (antispam.mines-paristech.fr) with ESMTPS id 61D6D55FB4; Fri, 17 Jan 2020 15:18:05 +0100 (CET) Received: from pseudopolis-wln.cri.ensmp.fr (adsl.cri.pck.nerim.net [213.41.146.223]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by smtp-4.mines-paristech.fr (Postfix) with ESMTPSA id 926582C030E; Fri, 17 Jan 2020 15:17:59 +0100 (CET) Date: Fri, 17 Jan 2020 15:17:34 +0100 (CET) From: Fabien COELHO X-X-Sender: fabien@pseudo To: szx9231@gmail.com, PostgreSQL Bugs Subject: Re: BUG #16216: the result of to_date function with negative year number not same as BC year number In-Reply-To: <16216-68da08087f3aeb78@postgresql.org> Message-ID: References: <16216-68da08087f3aeb78@postgresql.org> User-Agent: Alpine 2.21 (DEB 202 2017-01-01) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-CLAMAV-SCAN: ok X-VRSPAM-SCORE: -100 X-VRSPAM-STATE: legit X-VRSPAM-CAUSE: gggruggvucftvghtrhhoucdtuddrgedugedrtdejgdeivdcutefuodetggdotefrucfrrhhofhhilhgvmecuggftfghnshhusghstghrihgsvgenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurhepfffhvffujgfkfhgfgggtsehttdertddtredvnecuhfhrohhmpefhrggsihgvnhcuvefqgffnjffquceotghovghlhhhosegtrhhirdgvnhhsmhhprdhfrheqnecukfhppedvudefrdeguddrudegiedrvddvfeenucfrrghrrghmpehmohguvgepshhmthhpohhuth X-VRSPAM-EXTCAUSE: mhhouggvpehsmhhtphhouhht List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello, > example: > postgres=# select to_date('-120', 'yyyy'); > to_date > --------------- > 0121-01-01 BC > (1 row) > > postgres=# select to_date('120bc', 'yyyybc'); > to_date > --------------- > 0120-01-01 BC > (1 row) > > I think -120 means 120bc, however, the output is 121bc not 120bc. ISTM that the documentation does not say that -120 is supported as meaning BC. Given that there are no year 0 (zero was invented much later), history skips form year -1 to year 1. If -120 means in the implementation "year as an integer", then skipping zero would mean that it indeed represents year 121 BC, i.e. 120 years before "Jan 1, 1 AD". So there is a logic. BTW I found another oddity while trying strange date patterns: sql> SELECT DATE 'Jan 1, 0001 AD'; # 0001-01-01 But: sql> SELECT DATE 'Jan 1, 1 AD'; # 2001-01-01 # WT*? I'll try to have a look over the week-end. -- Fabien.