From: John Lumby <johnlumby@hotmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: value returned by EXTRACT, date_part
Date: Sat, 29 Aug 2020 09:13:46 -0400
Message-ID: <DM6PR06MB55625C1A9319BC921F4FB4ACA3530@DM6PR06MB5562.namprd06.prod.outlook.com> (raw)
In-Reply-To: <3517945.1598665173@sss.pgh.pa.us>
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>
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
> .
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnlumby@hotmail.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: value returned by EXTRACT, date_part
In-Reply-To: <DM6PR06MB55625C1A9319BC921F4FB4ACA3530@DM6PR06MB5562.namprd06.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox