Re: Intervals and ISO 8601 duration
On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <tgl@sss.pgh.pa.us> 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;
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
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
Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
В списке pgsql-general по дате отправления: