pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedIntervals and ISO 8601 duration
24+ messages / 10 participants
[nested] [flat]
* Intervals and ISO 8601 duration
@ 2023-01-12 13:40 Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Sebastien Flaesch @ 2023-01-12 13:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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. Other SQL engines like Oracle and Informix also have 2 classes of interval types.
However, the ISO-8601 standard format for durations allows to specify year/month with day to second parts, for example:
P2Y10M15DT10H30M20S
Seems PostgreSQL accepts this format in input.
But what does it mean exactly?
What is the actual INTERVAL value and INTERVAL class of this?
Testing with V15.1:
What is the interval class in this case:
test1=> select cast('P2Y10M15DT10H30M20S' as interval);
interval
----------------------------------
2 years 10 mons 15 days 10:30:20
(1 row)
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?
When using invalid INTERVAL fields, error is raised as expected:
test1=> select cast('P2Y10M15DT10H30M20S' as interval year to second);
ERROR: syntax error at or near "second"
LINE 1: ...lect cast('P2Y10M15DT10H30M20S' as interval year to second);
Does PostgreSQL assume that a month is ~30 days?
I did not find details about this in the documentation.
Thanks in advance!
Seb
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
@ 2023-01-12 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2023-01-12 15:08 UTC (permalink / raw)
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Sebastien Flaesch <sebastien.flaesch@4js.com> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-13 22:17 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:47 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
0 siblings, 2 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-13 22:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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.")
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
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-13 22:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:49 ` Re: Intervals and ISO 8601 duration David G. Johnston <david.g.johnston@gmail.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
1 sibling, 2 replies; 24+ messages in thread
From: Adrian Klaver @ 2023-01-13 22:41 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 1/13/23 14:17, Ken Tanzer wrote:
> On Thu, Jan 12, 2023 at 7:08 AM Tom Lane <tgl@sss.pgh.pa.us
> <mailto: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.")
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.klaver@aklaver.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-13 22:49 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 24+ messages in thread
From: David G. Johnston @ 2023-01-13 22:49 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Ken Tanzer <ken.tanzer@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, Jan 13, 2023 at 3:41 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 1/13/23 14:17, Ken Tanzer wrote:
> > On Thu, Jan 12, 2023 at 7:08 AM Tom Lane <tgl@sss.pgh.pa.us
> > <mailto: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.")
>
> 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.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-13 22:51 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:03 ` Re: Intervals and ISO 8601 duration Bryn Llewellyn <bryn@yugabyte.com>
1 sibling, 2 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-13 22:51 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
(1)
https://www.postgresql.org/message-id/flat/CAJKTcg9jLfH%2B-v8TS2gV%3DvZzqjLZ5cYyMVUtGxwyRitER5htMg%4...
Cheers,
Ken
>
> >
> > Cheers,
> > Ken
> >
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
--
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
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-13 23:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
1 sibling, 1 reply; 24+ messages in thread
From: Adrian Klaver @ 2023-01-13 23:03 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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.klaver@aklaver.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-13 23:32 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:57 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-13 23:32 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
(*) 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
*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
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-13 23:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
1 sibling, 1 reply; 24+ messages in thread
From: Adrian Klaver @ 2023-01-13 23:44 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 1/13/23 15:32, Ken Tanzer wrote:
> On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto: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
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(<interval>,'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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
> --
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-14 00:28 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Ken Tanzer @ 2023-01-14 00:28 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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:
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/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-14 00:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2023-01-14 00:57 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
Ken Tanzer <ken.tanzer@gmail.com> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-14 01:24 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 02:16 ` Re: Intervals and ISO 8601 duration Tatsuo Ishii <ishii@sraoss.co.jp>
2023-01-14 05:27 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-14 01:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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;
-[ 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
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
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-14 02:16 ` Tatsuo Ishii <ishii@sraoss.co.jp>
2023-01-14 05:20 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 24+ messages in thread
From: Tatsuo Ishii @ 2023-01-14 02:16 UTC (permalink / raw)
To: ken.tanzer@gmail.com; +Cc: tgl@sss.pgh.pa.us; adrian.klaver@aklaver.com; sebastien.flaesch@4js.com; pgsql-general@lists.postgresql.org
> 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 am not sure if I fully understand what you want to do here but I
guess you can extract "93600000" 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.000000 | 93600.000000
(1 row)
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 02:16 ` Re: Intervals and ISO 8601 duration Tatsuo Ishii <ishii@sraoss.co.jp>
@ 2023-01-14 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 24+ messages in thread
From: Tom Lane @ 2023-01-14 05:20 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@sraoss.co.jp>; +Cc: ken.tanzer@gmail.com; adrian.klaver@aklaver.com; sebastien.flaesch@4js.com; pgsql-general@lists.postgresql.org
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> I am not sure if I fully understand what you want to do here but I
> guess you can extract "93600000" 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-14 05:27 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 06:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
1 sibling, 1 reply; 24+ messages in thread
From: Adrian Klaver @ 2023-01-14 05:27 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 05:27 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-14 06:17 ` Ken Tanzer <ken.tanzer@gmail.com>
0 siblings, 0 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-14 06:17 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
--
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
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-13 23:57 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:02 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 24+ messages in thread
From: Adrian Klaver @ 2023-01-13 23:57 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 1/13/23 15:32, Ken Tanzer wrote:
> On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver <adrian.klaver@aklaver.com
> 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.000000 | 93600.000000 | t
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
> --
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:57 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-01-14 00:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 24+ messages in thread
From: Adrian Klaver @ 2023-01-14 00:02 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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 <adrian.klaver@aklaver.com
>
>> 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.000000 | 93600.000000 | 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>>
>>
>>
>> --
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-14 00:03 ` Bryn Llewellyn <bryn@yugabyte.com>
2023-01-14 00:07 ` Re: Intervals and ISO 8601 duration Martin L. Buchanan <martinlbuchanan@gmail.com>
2023-01-14 00:08 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 2 replies; 24+ messages in thread
From: Bryn Llewellyn @ 2023-01-14 00:03 UTC (permalink / raw)
To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com, Adrian Klaver <adrian.klaver@aklaver.com>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general list <pgsql-general@lists.postgresql.org>
> ken.tanzer@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-o...
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/ty...
The user-defined "strict equals" interval-interval "==“ operator
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/ty...
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:03 ` Re: Intervals and ISO 8601 duration Bryn Llewellyn <bryn@yugabyte.com>
@ 2023-01-14 00:07 ` Martin L. Buchanan <martinlbuchanan@gmail.com>
2023-01-14 05:32 ` Re: Intervals and ISO 8601 duration Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 1 reply; 24+ messages in thread
From: Martin L. Buchanan @ 2023-01-14 00:07 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; +Cc: Ken Tanzer <ken.tanzer@gmail.com>; Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com, Adrian Klaver <adrian.klaver@aklaver.com>; Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general list <pgsql-general@lists.postgresql.org>
On Fri, Jan 13, 2023 at 5:03 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> ken.tanzer@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-o...
>
> 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/ty...
>
> The user-defined "strict equals" interval-interval "==“ operator
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/ty...
>
*****
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:03 ` Re: Intervals and ISO 8601 duration Bryn Llewellyn <bryn@yugabyte.com>
2023-01-14 00:07 ` Re: Intervals and ISO 8601 duration Martin L. Buchanan <martinlbuchanan@gmail.com>
@ 2023-01-14 05:32 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-01-14 06:01 ` Re: Intervals and ISO 8601 duration Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Peter J. Holzer @ 2023-01-14 05:32 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 0x0000_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.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:03 ` Re: Intervals and ISO 8601 duration Bryn Llewellyn <bryn@yugabyte.com>
2023-01-14 00:07 ` Re: Intervals and ISO 8601 duration Martin L. Buchanan <martinlbuchanan@gmail.com>
2023-01-14 05:32 ` Re: Intervals and ISO 8601 duration Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2023-01-14 06:01 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 24+ messages in thread
From: Pavel Stehule @ 2023-01-14 06:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
so 14. 1. 2023 v 6:32 odesílatel Peter J. Holzer <hjp-pgsql@hjp.at> 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 0x0000_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.
> |_|_) | |
> | | | hjp@hjp.at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Re: Intervals and ISO 8601 duration Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:51 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:03 ` Re: Intervals and ISO 8601 duration Bryn Llewellyn <bryn@yugabyte.com>
@ 2023-01-14 00:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 24+ messages in thread
From: Adrian Klaver @ 2023-01-14 00:08 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; Ken Tanzer <ken.tanzer@gmail.com>; +Cc: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com, Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general list <pgsql-general@lists.postgresql.org>
On 1/13/23 16:03, Bryn Llewellyn wrote:
>> ken.tanzer@gmail.com <mailto:ken.tanzer@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.klaver@aklaver.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Intervals and ISO 8601 duration
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Re: Intervals and ISO 8601 duration Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Re: Intervals and ISO 8601 duration Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-13 22:47 ` Ken Tanzer <ken.tanzer@gmail.com>
1 sibling, 0 replies; 24+ messages in thread
From: Ken Tanzer @ 2023-01-13 22:47 UTC (permalink / raw)
To: Martin L. Buchanan <martinlbuchanan@gmail.com>; +Cc: pgsql-general
(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 <
martinlbuchanan@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/ <http://agency-software.org/>*
>> *https://demo.agency-software.org/client
>> <https://demo.agency-software.org/client>*
>> ken.tanzer@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>> <agency-general-request@lists.sourceforge.net?body=subscribe> 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/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2023-01-14 06:17 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-12 13:40 Intervals and ISO 8601 duration Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-01-12 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-13 22:17 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 22:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:49 ` David G. Johnston <david.g.johnston@gmail.com>
2023-01-13 22:51 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 23:32 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:28 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 00:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 01:24 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-14 02:16 ` Tatsuo Ishii <ishii@sraoss.co.jp>
2023-01-14 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-14 05:27 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 06:17 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-13 23:57 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-14 00:03 ` Bryn Llewellyn <bryn@yugabyte.com>
2023-01-14 00:07 ` Martin L. Buchanan <martinlbuchanan@gmail.com>
2023-01-14 05:32 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-01-14 06:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-01-14 00:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-13 22:47 ` Ken Tanzer <ken.tanzer@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox