computing dT from an interval

"Michael J. Baars" <mjbaars1977.pgsql-hackers@cyberfiber.eu> writes:

Can someone please tell me which of these two queries gives the correct result and which one the incorrect?

// 2.922 (&)
with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from "00" ) +
extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) / 24 as dT from A1;

// 2.88 (X)
with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes from "00" ) /
1440 + extract ( seconds from "00" ) / 86400 as dT from A1;

They'e both "incorrect", for some value of "incorrect". Quantities like
years, days, and seconds don't interconvert freely, which is why the
interval datatype tries to keep them separate.

In the first case, the main approximation is introduced when you do

select extract ( epoch from interval '8 years' );
date_part
-----------
252460800
(1 row)

If you do the math, you'll soon see that that corresponds to assuming
365.25 days (of 86400 seconds each) per year. So that's already wrong;
no year contains fractional days.

In the second case, the trouble starts with

select interval '8 years' / 1000;
?column?
-----------------
2 days 21:07:12
(1 row)

Internally, '8 years' is really 96 months, but to divide by 1000 we
have to down-convert that into the lesser units of days and seconds.
The approximation that's used for that is that months have 30 days,
so we initially get 2.88 days, and then the 0.88 days part is
converted to 76032 seconds.

So yeah, you can poke a lot of holes in these choices, but different
choices would just be differently inconsistent. The Gregorian calendar
is not very rational.

Personally I stay away from applying interval multiplication/division
to anything except intervals expressed in seconds. As soon as you
get into the larger units, you're forced to make unsupportable
assumptions.

regards, tom lane

On Sat, 2021-02-20 at 11:20 -0500, Tom Lane wrote:

"Michael J. Baars" <mjbaars1977.pgsql-hackers@cyberfiber.eu> writes:

Can someone please tell me which of these two queries gives the correct result and which one the incorrect?
// 2.922 (&)
with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from "00" ) +
extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) / 24 as dT from A1;
// 2.88 (X)
with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes from "00" )
/
1440 + extract ( seconds from "00" ) / 86400 as dT from A1;

They'e both "incorrect", for some value of "incorrect". Quantities like
years, days, and seconds don't interconvert freely, which is why the
interval datatype tries to keep them separate.

In the first case, the main approximation is introduced when you do

select extract ( epoch from interval '8 years' );
date_part
-----------
252460800
(1 row)

If you do the math, you'll soon see that that corresponds to assuming
365.25 days (of 86400 seconds each) per year. So that's already wrong;
no year contains fractional days.

I don't see the problem in this, we have 6 years of 365 days and 2 years of 366 days. Using this dt, we can compute a set of equidistant time stamps with their
corresponding values. Only the first and last row need to be on certain specific points in time, n * dt for certain n does not need to end up on january 1st for
each year in the interval.

Actually I only need to know for the moment, if dt spans more or less than one week and more or less than one day :)

In the second case, the trouble starts with

select interval '8 years' / 1000;
?column?
-----------------
2 days 21:07:12
(1 row)

Internally, '8 years' is really 96 months, but to divide by 1000 we
have to down-convert that into the lesser units of days and seconds.
The approximation that's used for that is that months have 30 days,
so we initially get 2.88 days, and then the 0.88 days part is
converted to 76032 seconds.

So yeah, you can poke a lot of holes in these choices, but different
choices would just be differently inconsistent. The Gregorian calendar
is not very rational.

Personally I stay away from applying interval multiplication/division
to anything except intervals expressed in seconds. As soon as you
get into the larger units, you're forced to make unsupportable
assumptions.

So how do you compute the number of seconds in 8 years?

I really think the first one does give the correct answer. The only thing is that the second one, the most trivial one of the two, does not give the same answer
as the first. They should have returned exactly the same number if you ask me.

regards, tom lane

Regards,
Mischa Baars.

"Michael J. Baars" <mjbaars1977.pgsql-hackers@cyberfiber.eu> writes:

So how do you compute the number of seconds in 8 years?

IMO, that's a meaningless computation, because the answer is not fixed.
Before you claim otherwise, think about the every-four-hundred-years
leap year exception in the Gregorian rules. Besides, what if the
question is "how many seconds in 7 years"? Then it definitely varies
depending on the number of leap days included.

What does make sense is timestamp subtraction, where the actual
endpoints of the interval are known.

regards, tom lane

On Mon, Feb 22, 2021 at 10:52:42AM -0500, Tom Lane wrote:

"Michael J. Baars" <mjbaars1977.pgsql-hackers@cyberfiber.eu> writes:

So how do you compute the number of seconds in 8 years?

IMO, that's a meaningless computation, because the answer is not fixed.
Before you claim otherwise, think about the every-four-hundred-years
leap year exception in the Gregorian rules. Besides, what if the
question is "how many seconds in 7 years"? Then it definitely varies
depending on the number of leap days included.

What does make sense is timestamp subtraction, where the actual
endpoints of the interval are known.

True.

I'm not sure whether this is a bug or an infelicity we document, but
at least in some parts of the world, this calculation doesn't comport
with the calendar in place at the time:

SELECT to_timestamp('1753', 'YYYY') - to_timestamp('1752', 'YYYY');
?column?
══════════
366 days
(1 row)

I'd like to imagine nobody will ever go mucking with the calendar to
the extent the British did that year, but one never knows.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

I'm not sure whether this is a bug or an infelicity we document, but
at least in some parts of the world, this calculation doesn't comport
with the calendar in place at the time:
SELECT to_timestamp('1753', 'YYYY') - to_timestamp('1752', 'YYYY');

Yeah, Appendix B.6 mentions that.

What isn't documented, and maybe should be, is the weird results
you get from the tzdata info for years before standardized time
zones came into use.

regression=# show timezone;
TimeZone
------------------
America/New_York
(1 row)

regression=# select '2020-01-01 00:00'::timestamptz;
timestamptz
------------------------
2020-01-01 00:00:00-05
(1 row)

regression=# select '1800-01-01 00:00'::timestamptz;
timestamptz
------------------------------
1800-01-01 00:00:00-04:56:02
(1 row)

If you're wondering where the heck that came from, it corresponds
to the actual longitude of New York City, i.e. local mean solar time.

regards, tom lane

On Mon, 2021-02-22 at 10:52 -0500, Tom Lane wrote:

"Michael J. Baars" <mjbaars1977.pgsql-hackers@cyberfiber.eu> writes:

So how do you compute the number of seconds in 8 years?

IMO, that's a meaningless computation, because the answer is not fixed.
Before you claim otherwise, think about the every-four-hundred-years
leap year exception in the Gregorian rules. Besides, what if the
question is "how many seconds in 7 years"? Then it definitely varies
depending on the number of leap days included.

What does make sense is timestamp subtraction, where the actual
endpoints of the interval are known.

regards, tom lane

There you have a point. Strange then that you get an answer other than 'undefined' when subtracting x - y, where y is undefined until x is defined, but you are
completely right. An interval of 8 years doesn't count a fixed number of seconds.

Thanks,
Mischa.