Re: Intervals and ISO 8601 duration

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_da

Re: Intervals and ISO 8601 duration

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

Dump (was: Intervals and ISO 8601 duration)

On 2023-01-14 06:32:03 +0100, Peter J. Holzer wrote: > 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

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

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 Perhap

Re: Intervals and ISO 8601 duration

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. rega

Re: Intervals and ISO 8601 duration

> 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

Re: Intervals and ISO 8601 duration

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() > keywor

Re: Intervals and ISO 8601 duration

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 g

Re: Intervals and ISO 8601 duration

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 i

Re: Intervals and ISO 8601 duration

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 c

Re: Intervals and ISO 8601 duration

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 abo

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

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 hou

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

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'::inte

Re: Intervals and ISO 8601 duration

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 e

Re: Intervals and ISO 8601 duration

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

Re: Intervals and ISO 8601 duration

(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-datetim

Re: Intervals and ISO 8601 duration

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 ask

Re: Intervals and ISO 8601 duration

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 bu

Re: Intervals and ISO 8601 duration

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 a

Intervals and ISO 8601 duration

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) It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes sense, since the number of days in a month can vary. Othe