Обсуждение: Intervals and ISO 8601 duration
On Thu, Jan 12, 2023 at 7:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
What Postgres actually stores for an interval is three fields:
months, days, and microseconds.
Is there a way to view/extract this raw data for any given interval?
(I'm asking because of an issue that came up about intervals that were "equal but not identical.")
(I'm asking because of an issue that came up about intervals that were "equal but not identical.")
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.
(resending--Martin didn't realize you hadn't sent to the list too.)
(1 row)
On Fri, Jan 13, 2023 at 2:28 PM Martin L. Buchanan <martinlbuchanan@gmail.com> wrote:
Dear Ken:You can extract individual subfields of interval as described here:
Hi Martin. I don't think that really gets at what's actually being stored. For example, look at a 2-year interval. Tom says they are being represented internally as Months, Days and Microseconds. But if you use extract it does not report anything for these values, because for example months which might be 24 internally is being reported as 0 because years is reported as 2:
SELECT EXTRACT(YEARS FROM '2 years'::interval) AS years, EXTRACT(MONTHS FROM '2 years'::interval) AS months, EXTRACT(DAYS FROM '2 years'::interval) AS days, EXTRACT(MICROSECONDS FROM '2 years'::interval) AS seconds;
SELECT EXTRACT(YEARS FROM '2 years'::interval) AS years, EXTRACT(MONTHS FROM '2 years'::interval) AS months, EXTRACT(DAYS FROM '2 years'::interval) AS days, EXTRACT(MICROSECONDS FROM '2 years'::interval) AS seconds;
years | months | days | seconds
-------+--------+------+---------
2 | 0 | 0 | 0
-------+--------+------+---------
2 | 0 | 0 | 0
(1 row)
--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Jan 13, 2023 at 2:41 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> (I'm asking because of an issue that came up about intervals that were
> "equal but not identical.")
1) Can you provide an example?
Here's an example. Note that they come out formatted differently with to_char, but evaluate as equal. The explanation(1) was that they were Equal but not Identical. I was thinking getting the raw data about how they are stored would get at the identicality issue:
WITH inters AS (
SELECT
'1 day 2 hours'::interval AS i1,
'26 hours'::interval AS i2
)
SELECT
*,
to_char(i1,'HH24:MM:SS') AS i1_char,
to_char(i2,'HH24:MM:SS') AS i2_char,
i1=i2 AS "Equal?"
FROM inters;
i1 | i2 | i1_char | i2_char | Equal?
----------------+----------+----------+----------+--------
1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
Cheers,
Ken
Ken
>
> Cheers,
> Ken
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
WITH inters AS (
SELECT
'1 day 2 hours'::interval AS i1,
'26 hours'::interval AS i2,
justify_interval('1 day 2 hours'::interval) AS ij1,
justify_interval('26 hours'::interval) AS ij2
)
SELECT
*,
to_char(justify_interval(i1),'HH24:MM:SS') AS i1_char,
to_char(justify_interval(i2),'HH24:MM:SS') AS i2_char,
i1=i2 AS "Equal?"
i1 | i2 | ij1 | ij2 | i1_char
| i2_char | Equal?
----------------+----------+----------------+----------------+----------+----------+--------
1 day 02:00:00 | 26:00:00 | 1 day 02:00:00 | 1 day 02:00:00 | 02:00:00
| 02:00:00 | t
I'm not quite sure what this is meant to convey. Maybe justify_hours and justify_days will return something that uniquely maps to the raw data, and maybe it doesn't (I can't figure that out!). But then there's still no justify_seconds or something that would get at the raw microseconds being stored.
And I could be wrong, but it seems like you were aiming towards making these two intervals the same. I was trying to zero in on the opposite--what is it that makes them different (not identical), and how to access that information. I was assuming that if they were not identical, the internal representation in Months, Days and Microseconds must be different--maybe that assumption is not valid. And maybe there is currently no way to get that raw representation. If that's the case, so be it, although I might then put in a small plug for it as a feature request. :) (*)
Cheers,
Ken
Ken
(*) These are probably bad suggestions, but something like...
EXTRACT ( (RAWHOURS|RAWDAYS|RAWMICROSECONDS) ... ?
Or a function RAWEXTRACT( HOURS|DAYS|MICROSECONDS ) ?
--
Adrian Klaver
adrian.klaver@aklaver.com
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Jan 13, 2023 at 3:44 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
If I am following what you want is to_char(<interval>,'HH24:MM:SS') to
be equal, correct?
Not really. My original question was:
[since intervals are stored internally as months, days and microseconds...]
> What Postgres actually stores for an interval is three fields:
> months, days, and microseconds.
Is there a way to view/extract this raw data for any given interval?
And again, I don't want to make anything equal, I'm looking for ways to get info about the non-identicalness.
I think we've established these two intervals are equal but not identical:
- '1 day 2 hours'::interval
- '26 hours'::interval2
Given that, my questions:
- Is the internal representation in months, days and microseconds different for these two intervals?
- (If no, what else is it that makes them non-identical?)
- Is there a way to access the internal representation?
And thanks to all of you who have responded!
Cheers,
Ken
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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.
Вложения
Вложения
On Fri, Jan 13, 2023 at 9:27 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/13/23 17:24, Ken Tanzer wrote:
> -[ 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.
Well technically it's reformatted, but in a way that apparently will equal the internal representation. So this helped me see what isn't identical about the two, while still being equal. So the answers are
1) Yes. (Months/Days/Ms 0/1/7200000 vs 0/0/93600000)
2) n/a
3) Yes, there's a way (or at least a functional equivalent) if you do some calculating... :)
Cheers,
Ken
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.