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. 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
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
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.
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
(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-3801Subscribe 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.
Import Notes
Reply to msg id not found: CAPfd9MkN42eCfqxSjFq7+NA9QtetodHebq1xPAm5fG05mxT02g@mail.gmail.com
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.
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)
/messages/by-id/CAJKTcg9jLfH+-v8TS2gV=vZzqjLZ5cYyMVUtGxwyRitER5htMg@mail.gmail.com
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.
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
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.
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 | tI'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-3801Subscribe 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
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-3801Subscribe 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
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
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:
/messages/by-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C@yugabyte.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
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 | tI 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-07Two 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:/messages/by-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C@yugabyte.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
type interval_mm_dd_ss_t as (mm, dd, ss)
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
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-07Two 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
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.
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
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.
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_totalFROM 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
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
