Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kBpqJ-0003hr-Hs for pgsql-sql@arkaria.postgresql.org; Sat, 29 Aug 2020 01:39:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kBpqI-0006Ue-BI for pgsql-sql@arkaria.postgresql.org; Sat, 29 Aug 2020 01:39:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kBpqI-0006Tw-4N for pgsql-sql@lists.postgresql.org; Sat, 29 Aug 2020 01:39:42 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kBpqE-000154-DT for pgsql-sql@lists.postgresql.org; Sat, 29 Aug 2020 01:39:41 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 07T1dX1k3517946; Fri, 28 Aug 2020 21:39:33 -0400 From: Tom Lane To: John Lumby cc: "David G. Johnston" , pgsql-sql Subject: Re: value returned by EXTRACT, date_part In-reply-to: References: Comments: In-reply-to John Lumby message dated "Fri, 28 Aug 2020 17:18:18 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3517944.1598665173.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 28 Aug 2020 21:39:33 -0400 Message-ID: <3517945.1598665173@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk John Lumby writes: > But also (and probably not easily changed now) it would make EXTRACT = > more generally useful if it included a fractional part for every field = > type where meaningful. > Is there any reason why it should not? The SQL spec says otherwise. Section 4.6.2 "Datetimes" in SQL:2011 defines timestamps as containing the six fields year, month, day, hour, minute, second (dates contain only the first three, times only the second three, but otherwise are similar), plus possibly timezone_hour and timezone_minute. All of these except the seconds field are specified to be integers. Furthermore, the EXTRACT function is specifically defined to return one of these fields. 4.6.3 "Intervals" lays down basically the same sorts of rules for intervals: they are made of component fields and only the seconds field can have a fractional part. PG does offer a nonstandard EPOCH "field" in EXTRACT, which tries to convert the timestamp or interval as a whole to some number of seconds. Possibly you could make use of that, perhaps after first applying date_trunc, to get what you're after. The whole enterprise is pretty shaky though; for example you cannot convert months to days or vice versa without making fundamentally-indefensible assumptions. regards, tom lane