Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Fri, Jan 13, 2023 at 9:27 PM Adrian Klaver 
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   | 720
> > i2_months | 0
> > i2_days   | 0
> > i2_msec   | 9360
> > equals| t
> > identical | f
> > i1_msec_total | 9360
> > i2_msec_total | 9360
>
> 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/720 vs 0/0/9360)
2) n/a
3) Yes, there's a way (or at least a functional equivalent) if you do some
calculating... :)

Cheers,
Ken


 --
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Pavel Stehule]

so 14. 1. 2023 v 6:32 odesílatel Peter J. Holzer  napsal:

> On 2023-01-13 17:07:17 -0700, Martin L. Buchanan wrote:
> > Just tried casting interval to bytea to see the binary layout, but that
> direct
> > cast is not allowed.
>
> A cast generally doesn't just reinterpret the same bit pattern as a
> different type, it converts the value.
>
> For example, in C (to choose a language "closer to the metal" than
> SQL), «int a = 3; float f = (float)a;» assigns 3.0 to f, not 4.2E-45
> (which would be 0x_0003 interpreted as an IEEE-754 single precision
> FP value).
>
> So there's no guarantee that a cast to bytea would have done what you
> expected even if it existed.
>
> Oracle has a function which returns the internal representation of a
> value as a series of (decimal) byte values. Back in the days when I was
> new to Oracle I used this to figure out how Oracle stores NUMBER, but
> now I've forgotten the name of the function. Maybe adding something like
> this to PostgreSQL would be worthwhile?
>

orafce has this function https://github.com/orafce/orafce

Regards

Pavel


>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Re: Intervals and ISO 8601 duration

[Peter J. Holzer]

On 2023-01-13 17:07:17 -0700, Martin L. Buchanan wrote:
> Just tried casting interval to bytea to see the binary layout, but that direct
> cast is not allowed.

A cast generally doesn't just reinterpret the same bit pattern as a
different type, it converts the value.

For example, in C (to choose a language "closer to the metal" than
SQL), «int a = 3; float f = (float)a;» assigns 3.0 to f, not 4.2E-45
(which would be 0x_0003 interpreted as an IEEE-754 single precision
FP value).

So there's no guarantee that a cast to bytea would have done what you
expected even if it existed.

Oracle has a function which returns the internal representation of a
value as a series of (decimal) byte values. Back in the days when I was
new to Oracle I used this to figure out how Oracle stores NUMBER, but
now I've forgotten the name of the function. Maybe adding something like
this to PostgreSQL would be worthwhile?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 17:24, Ken Tanzer wrote:
On Fri, Jan 13, 2023 at 4:57 PM Tom Lane > 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       | 720
i2_months     | 0
i2_days       | 0
i2_msec       | 9360
equals        | t
identical     | f
i1_msec_total | 9360
i2_msec_total | 9360


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/ /
/https://demo.agency-software.org/client 
/

ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Tom Lane]

Tatsuo Ishii  writes:
> I am not sure if I fully understand what you want to do here but I
> guess you can extract "9360" part easier using "EPOCH" of EXTRACT
> function.

EPOCH merges all three of the primitive fields together, which
is not what Ken is after IIUC.

regards, tom lane

Re: Intervals and ISO 8601 duration

[Tatsuo Ishii]

> 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   | 720
> i2_months | 0
> i2_days   | 0
> i2_msec   | 9360
> equals| t
> identical | f
> i1_msec_total | 9360
> i2_msec_total | 9360

I am not sure if I fully understand what you want to do here but I
guess you can extract "9360" part easier using "EPOCH" of EXTRACT
function.

SELECT EXTRACT(EPOCH FROM i1) AS epoch_i1, EXTRACT(EPOCH FROM i2) AS epoch_i2
FROM ( SELECT '1 day 2 hours'::interval AS i1, '26 hours'::interval AS i2) AS s;

   epoch_i1   |   epoch_i2   
--+--
 93600.00 | 93600.00
(1 row)

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Fri, Jan 13, 2023 at 4:57 PM Tom Lane  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   | 720
i2_months | 0
i2_days   | 0
i2_msec   | 9360
equals| t
identical | f
i1_msec_total | 9360
i2_msec_total | 9360

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Tom Lane]

Ken Tanzer  writes:
> 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?*

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.

regards, tom lane

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Fri, Jan 13, 2023 at 3:44 PM Adrian Klaver 
wrote:

> If I am following what you want is to_char(,'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:

   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?

And thanks to all of you who have responded!

Cheers,
Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 16:03, Bryn Llewellyn wrote:

ken.tan...@gmail.com  wrote:



I struggled to understand this whole murky area when I was writing the 
“Date and time data types and functionality” section for the YugabyteDB 
doc. (YugabyteDB uses the Postgres SQL processing code “as is” on top of 
its own distributed storage layer. All the examples in my doc work 
identically in vanilla PG.)


The implied question here is this: is the interval “1 day 2 hours” the 
same as the interval “26 hours”? It might seem that the answer is 
“yes”—as it surely must be. But, sorry to say, that the answer is 
actually “no”. Confused? You will be. Most people are until they’ve 
wrapped their head in a towel and puzzled it through for a few days. 


Or read the docs:

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-OUTPUT

"Internally interval values are stored as months, days, and 
microseconds. This is done because the number of days in a month varies, 
and a day can have 23 or 25 hours if a daylight savings time adjustment 
is involved."




This shows you what I mean:

set timezone = 'America/Los_Angeles';
with c as (
   select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as 
original_appointment)

select
   original_appointment::text as "original appointment",
   (original_appointment + '1 day 2 hours'::interval)::text as 
"postponed by '1_day 2 hours'",
   (original_appointment + '26 hours'::interval)::text as "postponed by 
'24_hours'"

from c;

This is the result:

   original appointment  | postponed by '1_day 2 hours' | postponed by 
'24_hours'

+--+-
  2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07       | 2023-03-12 
23:00:00-07


Two different answers! The “trick” here is that the time of the original 
appointment and the postponed times straddle the 2023 “spring forward” 
moment (at least as it happens in the America/Los_Angeles timezone). And 
the resolution of what at first might seem to be a bug come when you 
realized that you must make a distinction between clock time and 
calendar time.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Martin L. Buchanan]

On Fri, Jan 13, 2023 at 5:03 PM Bryn Llewellyn  wrote:

> ken.tan...@gmail.com wrote:
>
> 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
>
>
> I struggled to understand this whole murky area when I was writing the
> “Date and time data types and functionality” section for the YugabyteDB
> doc. (YugabyteDB uses the Postgres SQL processing code “as is” on top of
> its own distributed storage layer. All the examples in my doc work
> identically in vanilla PG.)
>
> The implied question here is this: is the interval “1 day 2 hours” the
> same as the interval “26 hours”? It might seem that the answer is “yes”—as
> it surely must be. But, sorry to say, that the answer is actually “no”.
> Confused? You will be. Most people are until they’ve wrapped their head in
> a towel and puzzled it through for a few days. This shows you what I mean:
>
> set timezone = 'America/Los_Angeles';
> with c as (
>   select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as
> original_appointment)
> select
>   original_appointment::text as "original appointment",
>   (original_appointment + '1 day 2 hours'::interval)::text as "postponed
> by '1_day 2 hours'",
>   (original_appointment + '26 hours'::interval)::text as "postponed by
> '24_hours'"
> from c;
>
> This is the result:
>
>   original appointment  | postponed by '1_day 2 hours' | postponed by
> '24_hours'
>
> +--+-
>  2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07   | 2023-03-12
> 23:00:00-07
>
> Two different answers! The “trick” here is that the time of the original
> appointment and the postponed times straddle the 2023 “spring forward”
> moment (at least as it happens in the America/Los_Angeles timezone). And
> the resolution of what at first might seem to be a bug come when you
> realized that you must make a distinction between clock time and calendar
> time.
>
> This query sheds a bit more light on the matter:
>
>
>
>
>
>
>
>
> *with c(i1, i2) as (  select '1 day 2 hours'::interval, '26
> hours'::interval)select  interval_mm_dd_ss(i1)::text as i1,
> interval_mm_dd_ss(i2)::text as i2,  (i1 = i2)::text as "i1 = i2",
> (i1==i2)::text as "i1 == i2"from c;*
>
> I defined the “interval_mm_dd_ss()” function and the “==” operator. (I
> called it the “strict equality operator for interval values”.)
>
> I believe that your question implies that you want
> my “interval_mm_dd_ss()” function. I can’t be sure what you want. But I
> dare to speculate that you might find it helpful to read (at least) the
> references that I’ve listed below. Start with the informal treatment in my
> blog post.
>
> Tom, Adrian, and David might remember my endless questions in this general
> space in March 2021. This, from Tom, answers the present question:
>
>
> https://www.postgresql.org/message-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C%40yugabyte.com
>
>
> p.s. Some other posts came in while I was writing this. My advice on
> “justify_interval()” is to avoid it.
> 
>
> PostgreSQL Timestamps and Timezones: How to Navigate the Interval Minefield
>
> https://www.yugabyte.com/blog/postgresql-timestamps-timezones-interval-minefield/
>
> Two ways of conceiving of time: calendar-time and clock-time
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/conceptual-background/#two-ways-of-conceiving-of-time-calendar-time-and-clock-time
>
> type interval_mm_dd_ss_t as (mm, dd, ss)
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#type-interval-mm-dd-ss-t-as-mm-dd-ss
>
> The user-defined "strict equals" interval-interval "==“ operator
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#the-user-defined-strict-equals-interval-interval-operator
>

*

Just tried casting interval to bytea to see the binary layout, but that
direct cast is not allowed.

Sincerely,

Martin L Buchanan
postgreSQL database developer (for about 2.5 years now)
(and not knowledgeable about administering PG or the internals of PG)
Laramie, WY, USA

Re: Intervals and ISO 8601 duration

[Bryn Llewellyn]

> ken.tan...@gmail.com wrote:
> 
> 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

I struggled to understand this whole murky area when I was writing the “Date 
and time data types and functionality” section for the YugabyteDB doc. 
(YugabyteDB uses the Postgres SQL processing code “as is” on top of its own 
distributed storage layer. All the examples in my doc work identically in 
vanilla PG.)

The implied question here is this: is the interval “1 day 2 hours” the same as 
the interval “26 hours”? It might seem that the answer is “yes”—as it surely 
must be. But, sorry to say, that the answer is actually “no”. Confused? You 
will be. Most people are until they’ve wrapped their head in a towel and 
puzzled it through for a few days. This shows you what I mean:

set timezone = 'America/Los_Angeles';
with c as (
  select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as 
original_appointment)
select
  original_appointment::text as "original appointment",
  (original_appointment + '1 day 2 hours'::interval)::text as "postponed by 
'1_day 2 hours'",
  (original_appointment + '26 hours'::interval)::text as "postponed by 
'24_hours'"
from c;

This is the result:

  original appointment  | postponed by '1_day 2 hours' | postponed by 
'24_hours' 
+--+-
 2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07   | 2023-03-12 23:00:00-07

Two different answers! The “trick” here is that the time of the original 
appointment and the postponed times straddle the 2023 “spring forward” moment 
(at least as it happens in the America/Los_Angeles timezone). And the 
resolution of what at first might seem to be a bug come when you realized that 
you must make a distinction between clock time and calendar time.

This query sheds a bit more light on the matter:

with c(i1, i2) as (
  select '1 day 2 hours'::interval, '26 hours'::interval)
select
  interval_mm_dd_ss(i1)::text as i1, 
  interval_mm_dd_ss(i2)::text as i2,
  (i1 = i2)::text as "i1 = i2",
  (i1==i2)::text as "i1 == i2"
from c;

I defined the “interval_mm_dd_ss()” function and the “==” operator. (I called 
it the “strict equality operator for interval values”.)

I believe that your question implies that you want my “interval_mm_dd_ss()” 
function. I can’t be sure what you want. But I dare to speculate that you might 
find it helpful to read (at least) the references that I’ve listed below. Start 
with the informal treatment in my blog post.

Tom, Adrian, and David might remember my endless questions in this general 
space in March 2021. This, from Tom, answers the present question:

https://www.postgresql.org/message-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C%40yugabyte.com
 

p.s. Some other posts came in while I was writing this. My advice on 
“justify_interval()” is to avoid it.


PostgreSQL Timestamps and Timezones: How to Navigate the Interval Minefield
https://www.yugabyte.com/blog/postgresql-timestamps-timezones-interval-minefield/

Two ways of conceiving of time: calendar-time and clock-time
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/conceptual-background/#two-ways-of-conceiving-of-time-calendar-time-and-clock-time

type interval_mm_dd_ss_t as (mm, dd, ss) 
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#type-interval-mm-dd-ss-t-as-mm-dd-ss

The user-defined "strict equals" interval-interval "==“ operator
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#the-user-defined-strict-equals-interval-interval-operator

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 15:57, Adrian Klaver wrote:

On 1/13/23 15:32, Ken Tanzer wrote:
On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver 


Cheers,
Ken

(*) These are probably bad suggestions, but something like...

      EXTRACT ( (RAWHOURS|RAWDAYS|RAWMICROSECONDS) ... ?
      Or a function RAWEXTRACT( HOURS|DAYS|MICROSECONDS ) ?


Close as I can get:


WITH inters AS (
     SELECT
     extract(epoch from '1 day 2 hours'::interval) AS i1,
     extract(epoch from '26 hours'::interval) AS i2
)
SELECT
     i1,
     i2,
     i1=i2 AS "Equal?"
FROM inters;

  i1  |  i2  | Equal?
--+--+
  93600.00 | 93600.00 | t


Or:

WITH inters AS (
SELECT
make_interval(secs=>extract(epoch from  '1 day 2 
hours'::interval)) as i1,
make_interval(secs=>extract(epoch from  '26 hours'::interval)) 
as i2

)
SELECT
i1,
i2,
i1=i2 AS "Equal?"
FROM inters;
i1|i2| Equal?
--+--+
 26:00:00 | 26:00:00 | t






    --     Adrian Klaver
    adrian.kla...@aklaver.com 



--


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 15:32, Ken Tanzer wrote:
On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver 


Cheers,
Ken

(*) These are probably bad suggestions, but something like...

      EXTRACT ( (RAWHOURS|RAWDAYS|RAWMICROSECONDS) ... ?
      Or a function RAWEXTRACT( HOURS|DAYS|MICROSECONDS ) ?


Close as I can get:


WITH inters AS (
SELECT
extract(epoch from '1 day 2 hours'::interval) AS i1,
extract(epoch from '26 hours'::interval) AS i2
)
SELECT
i1,
i2,
i1=i2 AS "Equal?"
FROM inters;

 i1  |  i2  | Equal?
--+--+
 93600.00 | 93600.00 | t




-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org/ /
/https://demo.agency-software.org/client 
/

ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 15:32, Ken Tanzer wrote:
On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver > 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 


This:

 WITH inters AS (
SELECT
'1 day 2 hours'::interval AS i1,
'26 hours'::interval AS i2
)
SELECT
i1=i2 AS "Equal?"
FROM inters;

say the intervals are equal.

If I am following what you want is to_char(,'HH24:MM:SS') to 
be equal, correct?



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

(*) These are probably bad suggestions, but something like...

      EXTRACT ( (RAWHOURS|RAWDAYS|RAWMICROSECONDS) ... ?
      Or a function RAWEXTRACT( HOURS|DAYS|MICROSECONDS ) ?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org/ /
/https://demo.agency-software.org/client 
/

ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver 
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

(*) These are probably bad suggestions, but something like...

 EXTRACT ( (RAWHOURS|RAWDAYS|RAWMICROSECONDS) ... ?
 Or a function RAWEXTRACT( HOURS|DAYS|MICROSECONDS ) ?


-- 
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 14:51, Ken Tanzer wrote:

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;



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


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Fri, Jan 13, 2023 at 2:41 PM Adrian Klaver 
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





(1)
https://www.postgresql.org/message-id/flat/CAJKTcg9jLfH%2B-v8TS2gV%3DvZzqjLZ5cYyMVUtGxwyRitER5htMg%40mail.gmail.com

Cheers,
Ken




>
> >
> > Cheers,
> > Ken
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[David G. Johnston]

On Fri, Jan 13, 2023 at 3:41 PM Adrian Klaver 
wrote:

> On 1/13/23 14:17, Ken Tanzer wrote:
> > On Thu, Jan 12, 2023 at 7:08 AM Tom Lane  > > 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.")
>
> 1) Can you provide an example?
>
> 2) I don't know how to reverse an output interval to it's input value.
>
>
That wasn't the ask though:

select interval '2 year 36 hours 15.123456789 seconds';
yields:
2 years 36:00:15.123457

But if what is stored is only months, days, and microseconds what is being
requested is to produce:

24 months 1.5 days 15123457 microseconds (or whatever the values stored in
those three positions is...)

David J.

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

(resending--Martin didn't realize you hadn't sent to the list too.)

On Fri, Jan 13, 2023 at 2:28 PM Martin L. Buchanan <
martinlbucha...@gmail.com> wrote:

> Dear Ken:
>
> You can extract individual subfields of interval as described here:
>
>
> https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
>
>
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;

 years | months | days | seconds
---++--+-
 2 |  0 |0 |   0

(1 row)


>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://demo.agency-software.org/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Adrian Klaver]

On 1/13/23 14:17, Ken Tanzer wrote:
On Thu, Jan 12, 2023 at 7:08 AM Tom Lane > 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.")


1) Can you provide an example?

2) I don't know how to reverse an output interval to it's input value.




Cheers,
Ken




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intervals and ISO 8601 duration

[Ken Tanzer]

On Thu, Jan 12, 2023 at 7:08 AM Tom Lane  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.")

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Intervals and ISO 8601 duration

[Tom Lane]

Sebastien Flaesch  writes:
> PostgreSQL has the INTERVAL type, which can be defined with fields such as:
> INTERVAL YEAR TO MONTH(year-month class)
> INTERVAL DAY TO SECOND(p)   (day-second class)

You can also say just INTERVAL, without any of the restrictions.

> It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes
> sense,

It's not so much that it doesn't make sense as that the SQL standard
doesn't have such a spelling.  They enumerate a few allowed combinations
(I think that no-modifiers is one of them), and we accept those for
pro forma syntax compliance.

> Should the following convert to a day-second interval?

> test1=> select cast('P2Y10M15DT10H30M20S' as interval day to second);
>  interval
> --
>  2 years 10 mons 15 days 10:30:20
> (1 row)

> Should PostgreSQL not raise an SQL error in above cases?

We regard these modifiers as similar to precision restrictions in
numerics and timestamps: we will round off low-order fields to
match the typmod, but we will not throw away high-order fields.

This probably doesn't match the SQL spec in detail, but the
details of their datetime types are sufficiently brain-dead
that we've never worried about that too much (eg, they still
don't have a model for daylight-savings time, last I checked).

What Postgres actually stores for an interval is three fields:
months, days, and microseconds.  If we're forced to interconvert
between those units, we use 30 days = 1 month and 24 hours = 1 day,
but it's usually best to avoid doing that.

regards, tom lane