Have I found an interval arithmetic bug?
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment on the same line. I added whitespace by hand to line up the fields.
select interval '-1.7 years'; -- -1 years -8 mons
select interval '29.4 months'; -- 2 years 5 mons 12 days
select interval '-1.7 years 29.4 months'; -- 8 mons 12 days << wrong
select interval '29.4 months -1.7 years'; -- 9 mons 12 days
select interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12 days
select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12 days
As I reason it, the last four “select” statements are all semantically the same. They’re just different syntaxes to add the two intervals the the first two “select” statements use separately. There’s one odd man out. And I reason this one to be wrong. Is there a flaw in my reasoning?
Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4 months. But the fractional part of the months value doesn’t spread further down into days. However, the fractional part of “29.4 months” (12 days) _does_ spread further down into days. What’s the rationale for this asymmetry?
I can’t see that my observations here can be explained by the difference between calendar time and clock time. Here I’m just working with non-metric units like feet and inches. One year is just defined as 12 months. And one month is just defined as 30 days. All that stuff about adding a month to 3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) , and that other stuff about adding one day to 23:00 on the day before the “spring forward” moment taking you to 23:00 on the next day (i.w. when intervals are added to timestamps) is downstream of simply adding two intervals.
On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment on the
same line. I added whitespace by hand to line up the fields.select interval '-1.7 years'; -- -1 years -8 mons
select interval '29.4 months'; -- 2 years 5 mons 12
daysselect interval '-1.7 years 29.4 months'; -- 8 mons 12
days << wrong
select interval '29.4 months -1.7 years'; -- 9 mons 12
daysselect interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12
days
select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12
daysAs I reason it, the last four “select” statements are all semantically the
same. They’re just different syntaxes to add the two intervals the the first
two “select” statements use separately. There’s one odd man out. And I reason
this one to be wrong. Is there a flaw in my reasoning?
[Thread moved to hackers.]
Looking at your report, I thought I could easily find the cause, but it
wasn't obvious. What is happening is that when you cast a float to an
integer in C, it rounds toward zero, meaning that 8.4 rounds to 8 and
-8.4 rounds to -8. The big problem here is that -8.4 is rounding in a
positive direction, while 8.4 rounds in a negative direction. See this:
int(int(-8.4) + 29)
21
int(int(29) + -8.4)
20
When you do '-1.7 years' first, it become -8, and then adding 29 yields
21. In the other order, it is 29 - 8.4, which yields 20.6, which
becomes 20. I honestly had never studied this interaction, though you
would think I would have seen it before. One interesting issue is that
it only happens when the truncations happen to values with different
signs --- if they are both positive or negative, it is fine.
The best fix I think is to use rint()/round to round to the closest
integer, not toward zero. The attached patch does this in a few places,
but the code needs more research if we are happy with this approach,
since there are probably other cases. Using rint() does help to produce
more accurate results, thought the regression tests show no change from
this patch.
Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4
months. But the fractional part of the months value doesn’t spread further down
into days. However, the fractional part of “29.4 months” (12 days) _does_
spread further down into days. What’s the rationale for this asymmetry?
Yes, looking at the code, it seems we only spill down to one unit, not
more. I think we need to have a discussion if we want to change that.
I think the idea was that if you specify a non-whole number, you
probably want to spill down one level, but don't want it spilling all
the way to milliseconds, which is certainly possible.
I can’t see that my observations here can be explained by the difference
between calendar time and clock time. Here I’m just working with non-metric
units like feet and inches. One year is just defined as 12 months. And one
month is just defined as 30 days. All that stuff about adding a month to
3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) ,
and that other stuff about adding one day to 23:00 on the day before the
“spring forward” moment taking you to 23:00 on the next day (i.w. when
intervals are added to timestamps) is downstream of simply adding two
intervals.
Ah, seems you have done some research. ;-)
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Attachments:
interval.difftext/x-diff; charset=us-asciiDownload+6-6
Thread moved to hackers, with a patch.
---------------------------------------------------------------------------
On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment on the
same line. I added whitespace by hand to line up the fields.select interval '-1.7 years'; -- -1 years -8 mons
select interval '29.4 months'; -- 2 years 5 mons 12
daysselect interval '-1.7 years 29.4 months'; -- 8 mons 12
days << wrong
select interval '29.4 months -1.7 years'; -- 9 mons 12
daysselect interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12
days
select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12
daysAs I reason it, the last four “select” statements are all semantically the
same. They’re just different syntaxes to add the two intervals the the first
two “select” statements use separately. There’s one odd man out. And I reason
this one to be wrong. Is there a flaw in my reasoning?Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4
months. But the fractional part of the months value doesn’t spread further down
into days. However, the fractional part of “29.4 months” (12 days) _does_
spread further down into days. What’s the rationale for this asymmetry?I can’t see that my observations here can be explained by the difference
between calendar time and clock time. Here I’m just working with non-metric
units like feet and inches. One year is just defined as 12 months. And one
month is just defined as 30 days. All that stuff about adding a month to
3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) ,
and that other stuff about adding one day to 23:00 on the day before the
“spring forward” moment taking you to 23:00 on the next day (i.w. when
intervals are added to timestamps) is downstream of simply adding two
intervals.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
bruce@momjian.us wrote:
[Thread moved to hackers.] …The best fix I think is…
Bryn wrote: Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4 months. But the fractional part of the months value doesn’t spread further down into days. However, the fractional part of “29.4 months” (12 days) _does_ spread further down into days. What’s the rationale for this asymmetry?
Yes, looking at the code, it seems we only spill down to one unit, not more. I think we need to have a discussion if we want to change that. I think the idea was that if you specify a non-whole number, you probably want to spill down one level, but don't want it spilling all the way to milliseconds, which is certainly possible.
Thanks for the quick response, Bruce. I was half expecting (re the bug) an explanation that showed that I’d (once again) misunderstood a fundamental principle.
I should come clean about the larger context. I work for Yugabyte, Inc. We have a distributed SQL database that uses the Version 11.2 PostgreSQL C code for SQL processing “as is”.
https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/
The founders decided to document YugabyteDB’s SQL functionality explicitly rather than just to point to the published PostgreSQL doc. (There are some DDL differences that reflect the storage layer differences.) I’m presently documenting date-time functionality. This is why I’m so focused on understanding the semantics exactly and on understanding the requirements that the functionality was designed to meet. I’m struggling with interval functionality. I read this:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
« …field values can have fractional parts; for example '1.5 week' or '01:02:03.45'. Such input is converted to the appropriate number of months, days, and seconds for storage. When this would result in a fractional number of months or days, the fraction is added to the lower-order fields using the conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output. »
Notice that the doc says that spill-down goes all the way to seconds and not just one unit. This simple test is consistent with the doc (output follows the dash-dash comment):
select ('6.54321 months'::interval)::text as i; -- 6 mons 16 days 07:06:40.32
You see similar spill-down with this:
select ('876.54321 days'::interval)::text as i; -- 876 days 13:02:13.344
And so on down through the remaining smaller units. It’s only this test that doesn’t spill down one unit:
select ('6.54321 years'::interval)::text as i; -- 6 years 6 mons
This does suggest a straight bug rather than a case for committee debate about what might have been intended. What do you think, Bruce?
On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian <bruce@momjian.us> wrote:
Thread moved to hackers, with a patch.
---------------------------------------------------------------------------
Here is a link to that thread, for others who might be curious about it as
I was:
/messages/by-id/20210402180549.GF9270@momjian.us
I get why it can make sense to move a thread. But if when doing so you
post a link to the new thread, that would be appreciated. Thanks!
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.
Bruce:
Thanks for tackling this issue.
The patch looks good to me.
When you have time, can you include the places which were not covered by
the current diff ?
Cheers
On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian <bruce@momjian.us> wrote:
Show quoted text
On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment
on the
same line. I added whitespace by hand to line up the fields.
select interval '-1.7 years'; -- -1 years -8
mons
select interval '29.4 months'; -- 2 years 5
mons 12
days
select interval '-1.7 years 29.4 months'; -- 8
mons 12
days << wrong
select interval '29.4 months -1.7 years'; -- 9mons 12
days
select interval '-1.7 years' + interval '29.4 months'; -- 9
mons 12
days
select interval '29.4 months' + interval '-1.7 years'; -- 9mons 12
days
As I reason it, the last four “select” statements are all semantically
the
same. They’re just different syntaxes to add the two intervals the the
first
two “select” statements use separately. There’s one odd man out. And I
reason
this one to be wrong. Is there a flaw in my reasoning?
[Thread moved to hackers.]
Looking at your report, I thought I could easily find the cause, but it
wasn't obvious. What is happening is that when you cast a float to an
integer in C, it rounds toward zero, meaning that 8.4 rounds to 8 and
-8.4 rounds to -8. The big problem here is that -8.4 is rounding in a
positive direction, while 8.4 rounds in a negative direction. See this:int(int(-8.4) + 29)
21
int(int(29) + -8.4)
20When you do '-1.7 years' first, it become -8, and then adding 29 yields
21. In the other order, it is 29 - 8.4, which yields 20.6, which
becomes 20. I honestly had never studied this interaction, though you
would think I would have seen it before. One interesting issue is that
it only happens when the truncations happen to values with different
signs --- if they are both positive or negative, it is fine.The best fix I think is to use rint()/round to round to the closest
integer, not toward zero. The attached patch does this in a few places,
but the code needs more research if we are happy with this approach,
since there are probably other cases. Using rint() does help to produce
more accurate results, thought the regression tests show no change from
this patch.Further… there’s a notable asymmetry. The fractional part of “1.7 years”
is 8.4
months. But the fractional part of the months value doesn’t spread
further down
into days. However, the fractional part of “29.4 months” (12 days) _does_
spread further down into days. What’s the rationale for this asymmetry?Yes, looking at the code, it seems we only spill down to one unit, not
more. I think we need to have a discussion if we want to change that.
I think the idea was that if you specify a non-whole number, you
probably want to spill down one level, but don't want it spilling all
the way to milliseconds, which is certainly possible.I can’t see that my observations here can be explained by the difference
between calendar time and clock time. Here I’m just working withnon-metric
units like feet and inches. One year is just defined as 12 months. And
one
month is just defined as 30 days. All that stuff about adding a month to
3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leapyears) ,
and that other stuff about adding one day to 23:00 on the day before the
“spring forward” moment taking you to 23:00 on the next day (i.w. when
intervals are added to timestamps) is downstream of simply adding two
intervals.Ah, seems you have done some research. ;-)
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.comIf only the physical world exists, free will is an illusion.
On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment
on the
same line. I added whitespace by hand to line up the fields.
select interval '-1.7 years'; -- -1 years -8
mons
select interval '29.4 months'; -- 2 years 5
mons 12
days
select interval '-1.7 years 29.4 months'; -- 8
mons 12
days << wrong
select interval '29.4 months -1.7 years'; -- 9mons 12
days
select interval '-1.7 years' + interval '29.4 months'; -- 9
mons 12
days
select interval '29.4 months' + interval '-1.7 years'; -- 9mons 12
days
While maybe there is an argument to fixing the negative/positive rounding
issue - there is no way this gets solved without breaking the current
implementation
select interval '0.3 years' + interval '0.4 years' - interval '0.7 years' +
interval '0.1 years' should not equal 0 but it certainly does.
Unless we take the concept of 0.3 years = 3 months and move to something
along the lines of
1 year = 360 days
1 month = 30 days
so therefore
0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days
0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
0.7 years = 360 days * 0.7 = 252 days = 8 months 12 days
Then, and only if we don't go to any more than tenths of a year, does the
math work. Probably this should resolve down to seconds and then work
backwards - but unless we're looking at breaking the entire way it
currently resolves things - I don't think this is of much value.
Doing math on intervals is like doing math on rounded numbers - there is
always going to be a pile of issues because the level of precision just is
not good enough.
John
On Fri, Apr 2, 2021 at 01:05:42PM -0700, Bryn Llewellyn wrote:
I should come clean about the larger context. I work for Yugabyte, Inc. We have
a distributed SQL database that uses the Version 11.2 PostgreSQL C code for SQL
processing “as is”.https://blog.yugabyte.com/
distributed-postgresql-on-a-google-spanner-architecture-query-layer/The founders decided to document YugabyteDB’s SQL functionality explicitly
rather than just to point to the published PostgreSQL doc. (There are some DDL
differences that reflect the storage layer differences.) I’m presently
documenting date-time functionality. This is why I’m so focused on
understanding the semantics exactly and on understanding the requirements that
the functionality was designed to meet. I’m struggling with interval
functionality. I read this:
[Sorry, also moved this to hackers. I might normally do all the
discussion on general, with patches, and then move it to hackers, but
our PG 14 deadline is next week, so it is best to move it now in hopes
it can be addressed in PG 14.]
Sure, seems like a good idea.
https://www.postgresql.org/docs/current/datatype-datetime.html#
DATATYPE-INTERVAL-INPUT« …field values can have fractional parts; for example '1.5 week' or
'01:02:03.45'. Such input is converted to the appropriate number of months,
days, and seconds for storage. When this would result in a fractional number of
months or days, the fraction is added to the lower-order fields using the
conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5
month' becomes 1 month and 15 days. Only seconds will ever be shown as
fractional on output. »Notice that the doc says that spill-down goes all the way to seconds and not
just one unit. This simple test is consistent with the doc (output follows the
dash-dash comment):select ('6.54321 months'::interval)::text as i; -- 6 mons 16 days 07:06:40.32
You see similar spill-down with this:
select ('876.54321 days'::interval)::text as i; -- 876 days 13:02:13.344
And so on down through the remaining smaller units. It’s only this test that
doesn’t spill down one unit:select ('6.54321 years'::interval)::text as i; -- 6 years 6 mons
This does suggest a straight bug rather than a case for committee debate about
what might have been intended. What do you think, Bruce?
So, that gets into more detail. When I said "spill down one unit", I
was not talking about _visible_ units, but rather the three internal
units used by Postgres:
https://www.postgresql.org/docs/13/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
Internally interval values are stored as months, days, and seconds.
-------------------------
However, while that explains why years don't spill beyond months, it
doesn't explain why months would spill beyond days. This certainly
seems inconsistent.
I have modified the patch to prevent partial months from creating
partial hours/minutes/seconds, so the output is now at least consistent
based on the three units:
SELECT ('6.54321 years'::interval)::text as i;
i
----------------
6 years 7 mons
SELECT ('6.54321 months'::interval)::text as i;
i
----------------
6 mons 16 days
SELECT ('876.54321 days'::interval)::text as i;
i
-----------------------
876 days 13:02:13.344
Partial years keeps it in months, partial months takes it to days, and
partial days take it to hours/minutes/seconds. This seems like an
improvement.
This also changes the regression test output, I think for the better:
SELECT INTERVAL '1.5 weeks';
i
------------------
- 10 days 12:00:00
+ 10 daysThe new output is less precise, but probably closer to what the user
wanted.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Attachments:
interval.difftext/x-diff; charset=us-asciiDownload+15-15
On Fri, Apr 2, 2021 at 01:20:26PM -0700, Ken Tanzer wrote:
On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian <bruce@momjian.us> wrote:
Thread moved to hackers, with a patch.
---------------------------------------------------------------------------Here is a link to that thread, for others who might be curious about it as I
was:
/messages/by-id/20210402180549.GF9270@momjian.us
b3bdafbfeacab0dd8967ff2a3ebf7844I get why it can make sense to move a thread.� But if when doing so you post a
link to the new thread, that would be appreciated.� Thanks!
I didn't think anyone but the original poster, who was copied in the new
thread, would really care about this thread, but it seems I was wrong.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
On Fri, Apr 2, 2021 at 01:27:33PM -0700, Zhihong Yu wrote:
Bruce:
Thanks for tackling this issue.The patch looks good to me.
When you have time, can you include the places which were not covered by the
current diff ?
I have just posted a new version of the patch which I think covers all
the right areas.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
On Fri, Apr 2, 2021 at 02:00:03PM -0700, John W Higgins wrote:
On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce@momjian.us> wrote:
While maybe there is an argument to fixing the negative/positive rounding issue
- there is no way this gets solved without breaking the current implementationselect interval '0.3 years' + interval '0.4 years' - interval '0.7 years'�+
interval '0.1 years' should not equal 0 but it certainly does.
My new code returns 0.2 months for this, not zero:
SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years' + interval '0.1 years';
?column?
----------
2 mons
which is also wrong since:
SELECT interval '0.1 years';
interval
----------
1 mon
Unless we take the concept of 0.3 years = 3 months and move to something along
the lines of�1 year = 360 days
1 month = 30 days�so therefore�
0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days�
0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
0.7 years = 360 days * 0.7 = 252 days = 8 months 12 daysThen, and only if we don't go to any more than tenths of a year, does the math
work. Probably this should resolve down to seconds and then work backwards -
but unless we're looking at breaking the entire way it currently resolves
things - I don't think this is of much value.Doing math on intervals is like doing math on rounded numbers - there is always
going to be a pile of issues because the level of precision just is not good
enough.
I think the big question is what units do people want with fractional
values. I have posted a follow-up email that spills only for one unit,
which I think is the best approach.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Hi,
bq. My new code returns 0.2 months for this, not zero
Can you clarify (the output below that was 2 mons, not 0.2) ?
Thanks
On Fri, Apr 2, 2021 at 4:58 PM Bruce Momjian <bruce@momjian.us> wrote:
Show quoted text
On Fri, Apr 2, 2021 at 02:00:03PM -0700, John W Higgins wrote:
On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce@momjian.us> wrote:
While maybe there is an argument to fixing the negative/positiverounding issue
- there is no way this gets solved without breaking the current
implementation
select interval '0.3 years' + interval '0.4 years' - interval '0.7
years' +
interval '0.1 years' should not equal 0 but it certainly does.
My new code returns 0.2 months for this, not zero:
SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years' + interval '0.1 years';
?column?
----------
2 monswhich is also wrong since:
SELECT interval '0.1 years';
interval
----------
1 monUnless we take the concept of 0.3 years = 3 months and move to something
along
the lines of
1 year = 360 days
1 month = 30 daysso therefore
0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days
0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
0.7 years = 360 days * 0.7 = 252 days = 8 months 12 daysThen, and only if we don't go to any more than tenths of a year, does
the math
work. Probably this should resolve down to seconds and then work
backwards -
but unless we're looking at breaking the entire way it currently resolves
things - I don't think this is of much value.Doing math on intervals is like doing math on rounded numbers - there is
always
going to be a pile of issues because the level of precision just is not
good
enough.
I think the big question is what units do people want with fractional
values. I have posted a follow-up email that spills only for one unit,
which I think is the best approach.--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.comIf only the physical world exists, free will is an illusion.
On Fri, Apr 2, 2021 at 07:47:32PM -0400, Bruce Momjian wrote:
I have modified the patch to prevent partial months from creating
partial hours/minutes/seconds, so the output is now at least consistent
based on the three units:SELECT ('6.54321 years'::interval)::text as i;
i
----------------
6 years 7 monsSELECT ('6.54321 months'::interval)::text as i;
i
----------------
6 mons 16 daysSELECT ('876.54321 days'::interval)::text as i;
i
-----------------------
876 days 13:02:13.344Partial years keeps it in months, partial months takes it to days, and
partial days take it to hours/minutes/seconds. This seems like an
improvement.This also changes the regression test output, I think for the better:
SELECT INTERVAL '1.5 weeks'; i ------------------ - 10 days 12:00:00 + 10 daysThe new output is less precise, but probably closer to what the user
wanted.
Thinking some more about this, the connection between months and days is
very inaccurate, 30 days/month, but the connection between days and
hours/minutes/seconds is pretty accurate, except for leap days.
Therefore, returning "10 days 12:00:00" is in many ways better, but
returning hours/minutes/seconds for fractional months is very arbitrary
and suggests an accuracy that doesn't exist. However, I am afraid that
trying to enforce that distinction in the Postgres behavior would appear
very arbitrary, so what I did above is proabably the best I can do.
Here is another example of what we have:
SELECT INTERVAL '1.5 years';
interval
---------------
1 year 6 mons
SELECT INTERVAL '1.5 months';
interval
---------------
1 mon 15 days
SELECT INTERVAL '1.5 weeks';
interval
----------
10 days
SELECT INTERVAL '1.5 days';
interval
----------------
1 day 12:00:00
SELECT INTERVAL '1.5 hours';
interval
----------
01:30:00
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
bruce@momjian.us wrote:
I have just posted a new version of the patch which I think covers all the right areas.
I found the relevant email from you to pgsql-hackers here:
/messages/by-id/20210402234732.GA29125@momjian.us
You said:
I have modified the patch to prevent partial months from creating partial hours/minutes/seconds… Partial years keeps it in months, partial months takes it to days, and partial days take it to hours/minutes/seconds. This seems like an improvement.
I have written some PL/pgSQL code that faithfully emulates the behavior that I see in my present vanilla PostgreSQL Version 13.2 system in a wide range of tests. This is the key part:
m1 int not null := trunc(p.mo);
m_remainder numeric not null := p.mo - m1::numeric;
m int not null := trunc(p.yy*12) + m1;
d_real numeric not null := p.dd + m_remainder*30.0;
d int not null := floor(d_real);
d_remainder numeric not null := d_real - d::numeric;
s numeric not null := d_remainder*24.0*60.0*60.0 +
p.hh*60.0*60.0 +
p.mi*60.0 +
p.ss;
begin
return (m, d, s)::modeled_interval_t;
end;
These quantities:
p.yy, p.mo, p.dd, p.hh, p.mi, and p.ss
are the user’s parameterization. All are real numbers. Because non-integral values for years, months, days, hours, and minutes are allowed when you specify a value using the ::interval typecast, my reference doc must state the rules. I would have struggled to express these rules in prose—especially given the use both of trunc() and floor(). I would have struggled more to explain what requirements these rules meet.
I gather that by the time YugabyteDB has adopted your patch, my PL/pgSQL will no longer be a correct emulation. So I’ll re-write it then.
I intend to advise users always to constrain the values, when they specify an interval value explicitly, so the the years, months, days, hours, and minutes are integers. This is, after all, the discipline that the make_interval() built-in imposes. So I might recommend using only that.
On Fri, Apr 2, 2021 at 05:50:59PM -0700, Bryn Llewellyn wrote:
are the user’s parameterization. All are real numbers. Because non-integral
values for years, months, days, hours, and minutes are allowed when you specify
a value using the ::interval typecast, my reference doc must state the rules. I
would have struggled to express these rules in prose—especially given the use
both of trunc() and floor(). I would have struggled more to explain what
requirements these rules meet.
The fundamental issue is that while months, days, and seconds are
consistent in their own units, when you have to cross from one unit to
another, it is by definition imprecise, since the interval is not tied
to a specific date, with its own days-of-the-month and leap days and
daylight savings time changes. It feels like it is going to be
imprecise no matter what we do.
Adding to this is the fact that interval values are stored in C 'struct
tm' defined in libc's ctime(), where months are integers, so carrying
around non-integer month values until we get a final result would add a
lot of complexity, and complexity to a system that is by definition
imprecise, which doesn't seem worth it.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Hi,
I got a local build with second patch where:
yugabyte=# SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years';
?column?
----------
1 mon
I think the outcome is a bit unintuitive (I would expect result close to 0).
Cheers
On Fri, Apr 2, 2021 at 5:07 PM Zhihong Yu <zyu@yugabyte.com> wrote:
Show quoted text
Hi,
bq. My new code returns 0.2 months for this, not zeroCan you clarify (the output below that was 2 mons, not 0.2) ?
Thanks
On Fri, Apr 2, 2021 at 4:58 PM Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Apr 2, 2021 at 02:00:03PM -0700, John W Higgins wrote:
On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce@momjian.us> wrote:
While maybe there is an argument to fixing the negative/positiverounding issue
- there is no way this gets solved without breaking the current
implementation
select interval '0.3 years' + interval '0.4 years' - interval '0.7
years' +
interval '0.1 years' should not equal 0 but it certainly does.
My new code returns 0.2 months for this, not zero:
SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years' + interval '0.1 years';
?column?
----------
2 monswhich is also wrong since:
SELECT interval '0.1 years';
interval
----------
1 monUnless we take the concept of 0.3 years = 3 months and move to
something along
the lines of
1 year = 360 days
1 month = 30 daysso therefore
0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days
0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
0.7 years = 360 days * 0.7 = 252 days = 8 months 12 daysThen, and only if we don't go to any more than tenths of a year, does
the math
work. Probably this should resolve down to seconds and then work
backwards -
but unless we're looking at breaking the entire way it currently
resolves
things - I don't think this is of much value.
Doing math on intervals is like doing math on rounded numbers - there
is always
going to be a pile of issues because the level of precision just is not
good
enough.
I think the big question is what units do people want with fractional
values. I have posted a follow-up email that spills only for one unit,
which I think is the best approach.--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.comIf only the physical world exists, free will is an illusion.
On Fri, 2 Apr 2021 at 21:08, Zhihong Yu <zyu@yugabyte.com> wrote:
Hi,
I got a local build with second patch where:yugabyte=# SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years';
?column?
----------
1 monI think the outcome is a bit unintuitive (I would expect result close to
0).
That's not fundamentally different from this:
odyssey=> select 12 * 3/10 + 12 * 4/10 - 12 * 7/10;
?column?
----------
-1
(1 row)
odyssey=>
And actually the result is pretty close to 0. I mean it’s less than 0.1
year.
I wonder if it might have been better if only integers had been accepted
for the components? If you want 0.3 years write 0.3 * '1 year'::interval.
But changing it now would be a pretty significant backwards compatibility
break.
There's really no avoiding counterintuitive behaviour though. Look at this:
odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7
* '1 year'::interval;
?column?
------------------
-1 mons +30 days
(1 row)
odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7
* '1 year'::interval = '0';
?column?
----------
t
(1 row)
odyssey=>
In other words, doing the “same” calculation but with multiplying 1 year
intervals by floats to get the values to add, you end up with an interval
that while not identical to 0 does compare equal to 0. So very close to 0;
in fact, as close to 0 as you can get without actually being identically 0.
Hi,
The mix of interval and comparison with float is not easy to interpret. See
the following (I got 0.0833 since the result for interval '0.3 years' +
interval '0.4 years' - ... query was 1 month and 1/12 ~= 0.0833).
yugabyte=# select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7
* '1 year'::interval = '0.0833 year'::interval;
?column?
----------
f
As long as Bruce's patch makes improvements over the current behavior, I
think that's fine.
Cheers
On Fri, Apr 2, 2021 at 6:24 PM Isaac Morland <isaac.morland@gmail.com>
wrote:
Show quoted text
On Fri, 2 Apr 2021 at 21:08, Zhihong Yu <zyu@yugabyte.com> wrote:
Hi,
I got a local build with second patch where:yugabyte=# SELECT interval '0.3 years' + interval '0.4 years' -
interval '0.7 years';
?column?
----------
1 monI think the outcome is a bit unintuitive (I would expect result close to
0).That's not fundamentally different from this:
odyssey=> select 12 * 3/10 + 12 * 4/10 - 12 * 7/10;
?column?
----------
-1
(1 row)odyssey=>
And actually the result is pretty close to 0. I mean it’s less than 0.1
year.I wonder if it might have been better if only integers had been accepted
for the components? If you want 0.3 years write 0.3 * '1 year'::interval.
But changing it now would be a pretty significant backwards compatibility
break.There's really no avoiding counterintuitive behaviour though. Look at this:
odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7
* '1 year'::interval;
?column?
------------------
-1 mons +30 days
(1 row)odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7
* '1 year'::interval = '0';
?column?
----------
t
(1 row)odyssey=>
In other words, doing the “same” calculation but with multiplying 1 year
intervals by floats to get the values to add, you end up with an interval
that while not identical to 0 does compare equal to 0. So very close to 0;
in fact, as close to 0 as you can get without actually being identically 0.
On Fri, Apr 2, 2021 at 06:11:08PM -0700, Zhihong Yu wrote:
Hi,
I got a local build with second patch where:yugabyte=# SELECT �interval '0.3 years' + interval '0.4 years' -
� � � � � � � � interval '0.7 years';
�?column?
----------
�1 monI think the outcome is a bit unintuitive (I would expect result�close�to 0).
Uh, the current code returns:
SELECT interval '0.3 years' + interval '0.4 years' - interval '0.7 years';
?column?
----------
-1 mon
and with the patch it is:
SELECT interval '0.3 years' + interval '0.4 years' - interval '0.7 years';
?column?
----------
1 mon
What it isn't, is zero months, which is the obviously ideal answer.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
On Fri, Apr 2, 2021 at 07:06:08PM -0700, Zhihong Yu wrote:
Hi,
The mix of interval and comparison with float is not easy to interpret. See the
following (I got 0.0833 since the result for�interval '0.3 years' + interval
'0.4 years' - ... query was 1 month and 1/12 ~= 0.0833).yugabyte=# select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 *
'1 year'::interval = '0.0833 year'::interval;
�?column?
----------
�fAs long as Bruce's patch makes improvements over the current behavior, I think
that's fine.
I wish I could figure out how to improve it any futher. What is odd is
that I have never seen this reported as a problem before. I plan to
apply this early next week for PG 14.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
