Re: Intervals and ISO 8601 duration - Mailing list pgsql-general

On 1/13/23 17:24, Ken Tanzer wrote:
> On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto: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;
> 
> -[ 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@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

-- 
Adrian Klaver
adrian.klaver@aklaver.com