On 1/13/23 17:24, Ken Tanzer wrote:
On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:
Given what extract() provides,
stored months = years * 12 + months
stored days = days
stored usec = reconstruct from hours+minutes+seconds+microseconds
Perhaps it wouldn't be a bad idea to provide a couple more extract()
keywords to make that easier.
Thanks Tom! That helped me spell it out and understand it a little more
clearly. Both to understand the non-identicalness, and to see the
specifics. But yeah it would be nice if it was a little easier to
extract! :)
WITH foo AS (
WITH inters AS (
SELECT
'1 day 2 hours'::interval AS i1,
'26 hours'::interval AS i2
)
SELECT
*,
EXTRACT(YEARS FROM i1)*12 + EXTRACT(MONTHS FROM i1) AS i1_months,
EXTRACT(DAYS FROM i1) AS i1_days,
EXTRACT(HOURS FROM i1) * 60 * 60 * 1000
+ EXTRACT(MINUTES FROM i1) * 60 * 1000
+ EXTRACT(SECONDS FROM i1) * 1000
+ EXTRACT(MICROSECONDS FROM i1)
AS i1_msec,
EXTRACT(YEARS FROM i2)*12 + EXTRACT(MONTHS FROM i2) AS i2_months,
EXTRACT(DAYS FROM i2) AS i2_days,
EXTRACT(HOURS FROM i2) * 60 * 60 * 1000
+ EXTRACT(MINUTES FROM i2) * 60 * 1000
+ EXTRACT(SECONDS FROM i2) * 1000
+ EXTRACT(MICROSECONDS FROM i2)
AS i2_msec,
i1=i2 AS equals
FROM inters
)
SELECT
*,
(i1_months=i2_months AND i1_days=i2_days AND i1_msec=i2_msec) AS
identical,
i1_months * 30 * 24 * 60 * 60 * 1000
+ i1_days * 24 * 60 * 60 * 1000
+ i1_msec AS i1_msec_total,
i2_months * 30 * 24 * 60 * 60 * 1000
+ i2_days * 24 * 60 * 60 * 1000
+ i2_msec AS i2_msec_total
FROM foo;
-[ RECORD 1 ]-+---------------
i1 | 1 day 02:00:00
i2 | 26:00:00
i1_months | 0
i1_days | 1
i1_msec | 7200000
i2_months | 0
i2_days | 0
i2_msec | 93600000
equals | t
identical | f
i1_msec_total | 93600000
i2_msec_total | 93600000
I don't see how the above answers, from your previous post, the below:
1) Is the internal representation in months, days and microseconds
different for these two intervals?
2) (If no, what else is it that makes them non-identical?)
3) Is there a way to access the internal representation?
What you have done is reformat the intervals and establish that the
formatted values point back at equal and most probably identical values.
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org/ <http://agency-software.org/>/
/https://demo.agency-software.org/client
<https://demo.agency-software.org/client>/
ken.tanzer@xxxxxxxxxxxxxxxxxxx <mailto:ken.tanzer@xxxxxxxxxxxxxxxxxxx>
(253) 245-3801
Subscribe to the mailing list
<mailto:agency-general-request@xxxxxxxxxxxxxxxxxxxxx?body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
