On 24.02.22 03:35, Joseph Koshakow wrote:
However when executing EXTRACT we first truncate
DAYS_PER_YEAR to an integer, and then multiply it
by the total years in the Interval
/* this always fits into int64 */
secs_from_day_month = ((int64) DAYS_PER_YEAR * (interval->month /
MONTHS_PER_YEAR) +
(int64) DAYS_PER_MONTH * (interval->month %
MONTHS_PER_YEAR) +
interval->day) * SECS_PER_DAY;
Is this truncation on purpose? It seems like
EXTRACT is not accounting for leap years in
it's calculation.
This was not intentional. The cast is only to make the multiplication
happen in int64; it didn't mean to drop any fractional parts.
Oops I sent that to the wrong email. If this isn't intented I've created a patch
that fixes it, with the following two open questions
* DAYS_PER_YEAR_NUM is recalculated every time. Is there anyway
to convert a float directly to a numeric to avoid this?
We really wanted to avoid doing calculations in numeric as much as
possible. So we should figure out a different way to write this. The
attached patch works for me. It's a bit ugly since it hardcodes some
factors. Maybe we can rephrase it a bit more elegantly.From 0b7222beb5260c710d79c9a0573c3b39a64acf1b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 8 Apr 2022 13:16:25 +0200
Subject: [PATCH v1] Fix extract epoch from interval calculation
The new numeric code for extract epoch from interval accidentally
truncated the DAYS_PER_YEAR value to an integer, leading to results
that mismatched the floating-point interval_part calculations.
The commit a2da77cdb4661826482ebf2ddba1f953bc74afe4 that introduced
this actually contains the regression test change that this reverts.
I suppose this was missed at the time.
Reported-by: Joseph Koshakow <kosh...@gmail.com>
Discussion:
https://www.postgresql.org/message-id/flat/CAAvxfHd5n%3D13NYA2q_tUq%3D3%3DSuWU-CufmTf-Ozj%3DfrEgt7pXwQ%40mail.gmail.com
---
src/backend/utils/adt/timestamp.c | 6 +++---
src/test/regress/expected/interval.out | 4 ++--
2 files changed, 5 insertions(+), 5 deletions(-)
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index 1c0bf0aa5c..2677d27632 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5288,9 +5288,9 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
int64 val;
/* this always fits into int64 */
- secs_from_day_month = ((int64) DAYS_PER_YEAR *
(interval->month / MONTHS_PER_YEAR) +
- (int64)
DAYS_PER_MONTH * (interval->month % MONTHS_PER_YEAR) +
-
interval->day) * SECS_PER_DAY;
+ secs_from_day_month = ((int64) (4 * DAYS_PER_YEAR) *
(interval->month / MONTHS_PER_YEAR) +
+ (int64) (4 *
DAYS_PER_MONTH) * (interval->month % MONTHS_PER_YEAR) +
+ (int64) 4 *
interval->day) * SECS_PER_DAY/4;
/*---
* result = secs_from_day_month + interval->time /
1'000'000
diff --git a/src/test/regress/expected/interval.out
b/src/test/regress/expected/interval.out
index e4b1246f45..8e2d535543 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -953,11 +953,11 @@ SELECT f1,
@ 1 min | 0 | 0.000 | 0.000000 |
1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
60.000000
@ 5 hours | 0 | 0.000 | 0.000000 |
0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
18000.000000
@ 10 days | 0 | 0.000 | 0.000000 |
0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 |
864000.000000
- @ 34 years | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 |
1072224000.000000
+ @ 34 years | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 |
1072958400.000000
@ 3 mons | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 |
7776000.000000
@ 14 secs ago | -14000000 | -14000.000 | -14.000000 |
0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
-14.000000
@ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 |
3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
93784.000000
- @ 6 years | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 |
189216000.000000
+ @ 6 years | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 |
189345600.000000
@ 5 mons | 0 | 0.000 | 0.000000 |
0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 |
12960000.000000
@ 5 mons 12 hours | 0 | 0.000 | 0.000000 |
0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 |
13003200.000000
(10 rows)
base-commit: 9a7229948c70945ca6ef0b36adfe61b74f4fdaf5
--
2.35.1
