From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Intervals and ISO 8601 duration |
Date: | 2023-01-14 05:27:15 |
Message-ID: | dda7b10f-9738-4593-d392-4d6df44af1f9@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/13/23 17:24, Ken Tanzer wrote:
> On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-01-14 05:31:28 | Re: synchronized standby: committed local and waiting for remote ack |
Previous Message | Tom Lane | 2023-01-14 05:20:51 | Re: Intervals and ISO 8601 duration |