Subject: Re: value returned by EXTRACT, date_part
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-sql <pgsql-sql@lists.postgresql.org>
References: 
 <DM6PR06MB5562115198C798B1C06B32DBA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
 <CAKFQuwZBu3Hp__XKSWeBeqyep=UVd9-4x9nnLvfxxAW3hgCJfg@mail.gmail.com>
 <DM6PR06MB5562A231945B39D33623849BA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
 <3517945.1598665173@sss.pgh.pa.us>
From: John Lumby <johnlumby@hotmail.com>
Message-ID: 
 <DM6PR06MB55625C1A9319BC921F4FB4ACA3530@DM6PR06MB5562.namprd06.prod.outlook.com>
Date: Sat, 29 Aug 2020 09:13:46 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
In-Reply-To: <3517945.1598665173@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
Received: from 255.255.255.255 (255.255.255.255) by
 YT1PR01CA0041.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:b01:2e::10) with Microsoft
 SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3326.19 via Frontend
 Transport; Sat, 29 Aug 2020 13:13:29 +0000
X-Microsoft-Original-Message-ID: 
 <84943e41-834b-b51d-76ff-218184e613a0@hotmail.com>
X-TMN: [jQC+H7CzVptXTKXGRhqgyFdtqUMDx8O9kTAqakYBAHAu9EcoCFBNQoGTrPZf8Ebr]
X-MS-PublicTrafficType: Email
X-IncomingHeaderCount: 49
X-EOPAttributedMessage: 0
X-MS-Office365-Filtering-Correlation-Id: 99e85c95-8043-4723-9c3d-08d84c1d52b7
X-MS-TrafficTypeDiagnostic: BN8NAM04HT219:
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	99WrCDb9EyOghbT3jgcW2vpyt/QYaqHf4bJMk1KUZgPHC0k1bprJZsybTAxdOKE/Tms27AS7HS+pnMPeTEx4DXu7UzvQ/jrnXwu4z+ai9a7NyjBNDS4tALRg00jBqG+yLpZ9zTzr70tk8vMAvHZ5zkREg2C2C/glyvgG03WbC/wtM9ic+Cb51KSDTBVlyebFGaERfikd9BfR83bCMVeMfQ==
X-MS-Exchange-AntiSpam-MessageData: 
	E3XM4SDmK8eKu9Oi5UsjemZhUoSXvRu4QDwXbjY7aTTaeRD0+NDdrwpHRqKtR8R9jvZez0NQPJ9UfC0ehS4jds7fIjG2FAmxRvqgSOpsSIjHU4nkP0gkHCiEtEkS42ffYG5/Kw6W/4neYHfI4EuVDNp1LN6s9DONJ5BG/IisbRmxaot4I8Q9F+x8DNXxhG/rgt1Nrsh60ltYoEC6+u4QGw==
X-OriginatorOrg: hotmail.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 99e85c95-8043-4723-9c3d-08d84c1d52b7
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 29 Aug 2020 13:13:30.6447
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-AuthSource: 
	BN8NAM04FT054.eop-NAM04.prod.protection.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Anonymous
X-MS-Exchange-CrossTenant-FromEntityHeader: Internet
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN8NAM04HT219
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

Thanks Tom

On 2020-08-28 21:39, Tom Lane wrote:
>
> 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.

What is not clear to me is how the "components"  (aka "subfields") of an 
interval are defined.

e.g.


SELECT EXTRACT(days FROM INTERVAL '1 year 35 days 1 minute');
  date_part
-----------
         35

ok,     it takes the interval modulo months (the next higher unit than 
the one I requested) and then rounds that down.


But

SELECT EXTRACT(days FROM INTERVAL '400 days 1 minute');
  date_part
-----------
        400

oh!    no it doesn't ...


>
> 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.


Yes!        That is exactly what I am looking for.    Actually for 
simplicity I think I don't need date_trunc;

for this particular case of wanting to find the size of an interval,

it is as simple as always requesting its epoch and working in 
double-precision seconds.

Thanks!


>
> 			regards, tom lane
> .