On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <t...@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;

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

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.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Reply via email to