Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfc51c91

Browse files
committed
Properly round months into days and into seconds for interval
multiplication/division queries like select '41 mon 10:00:00'::interval/ 10 as "pos".Report from Michael Glaesemann
1 parent5120f88 commitfc51c91

File tree

2 files changed

+44
-36
lines changed

2 files changed

+44
-36
lines changed

‎src/backend/utils/adt/timestamp.c‎

Lines changed: 43 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.165 2006/07/13 16:49:16 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.166 2006/09/03 03:34:04 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2492,19 +2492,14 @@ interval_mul(PG_FUNCTION_ARGS)
24922492
{
24932493
Interval*span=PG_GETARG_INTERVAL_P(0);
24942494
float8factor=PG_GETARG_FLOAT8(1);
2495-
doublemonth_remainder,
2496-
day_remainder,
2497-
month_remainder_days;
2495+
doublemonth_remainder_days,sec_remainder;
2496+
int32orig_month=span->month,orig_day=span->day;
24982497
Interval*result;
24992498

25002499
result= (Interval*)palloc(sizeof(Interval));
25012500

2502-
month_remainder=span->month*factor;
2503-
day_remainder=span->day*factor;
2504-
result->month= (int32)month_remainder;
2505-
result->day= (int32)day_remainder;
2506-
month_remainder-=result->month;
2507-
day_remainder-=result->day;
2501+
result->month= (int32) (span->month*factor);
2502+
result->day= (int32) (span->day*factor);
25082503

25092504
/*
25102505
* The above correctly handles the whole-number part of the month and day
@@ -2516,16 +2511,31 @@ interval_mul(PG_FUNCTION_ARGS)
25162511
* using justify_hours and/or justify_days.
25172512
*/
25182513

2519-
/* fractional months full days into days */
2520-
month_remainder_days=month_remainder*DAYS_PER_MONTH;
2521-
result->day+= (int32)month_remainder_days;
2522-
/* fractional months partial days into time */
2523-
day_remainder+=month_remainder_days- (int32)month_remainder_days;
2514+
/*
2515+
*Fractional months full days into days.
2516+
*
2517+
*The remainders suffer from float rounding, so instead of
2518+
*doing the computation using just the remainder, we calculate
2519+
*the total number of days and subtract. Specifically, we are
2520+
*multipling by DAYS_PER_MONTH before dividing by factor.
2521+
*This greatly reduces rounding errors.
2522+
*/
2523+
month_remainder_days= (orig_month* (double)DAYS_PER_MONTH)*factor-
2524+
result->month* (double)DAYS_PER_MONTH;
2525+
sec_remainder= (orig_day* (double)SECS_PER_DAY)*factor-
2526+
result->day* (double)SECS_PER_DAY+
2527+
(month_remainder_days- (int32)month_remainder_days)*SECS_PER_DAY;
25242528

2529+
/* cascade units down */
2530+
result->day+= (int32)month_remainder_days;
25252531
#ifdefHAVE_INT64_TIMESTAMP
2526-
result->time=rint(span->time*factor+day_remainder*USECS_PER_DAY);
2532+
result->time=rint(span->time*factor+sec_remainder*USECS_PER_SEC);
25272533
#else
2528-
result->time=span->time*factor+day_remainder*SECS_PER_DAY;
2534+
/*
2535+
*TSROUND() needed to prevent -146:23:60.00 output on PowerPC for
2536+
*SELECT interval '-41 mon -12 days -360:00' * 0.3;
2537+
*/
2538+
result->time=span->time*factor+TSROUND(sec_remainder);
25292539
#endif
25302540

25312541
PG_RETURN_INTERVAL_P(result);
@@ -2546,39 +2556,37 @@ interval_div(PG_FUNCTION_ARGS)
25462556
{
25472557
Interval*span=PG_GETARG_INTERVAL_P(0);
25482558
float8factor=PG_GETARG_FLOAT8(1);
2549-
doublemonth_remainder,
2550-
day_remainder,
2551-
month_remainder_days;
2559+
doublemonth_remainder_days,sec_remainder;
2560+
int32orig_month=span->month,orig_day=span->day;
25522561
Interval*result;
2553-
2562+
25542563
result= (Interval*)palloc(sizeof(Interval));
25552564

25562565
if (factor==0.0)
25572566
ereport(ERROR,
25582567
(errcode(ERRCODE_DIVISION_BY_ZERO),
25592568
errmsg("division by zero")));
25602569

2561-
month_remainder=span->month /factor;
2562-
day_remainder=span->day /factor;
2563-
result->month= (int32)month_remainder;
2564-
result->day= (int32)day_remainder;
2565-
month_remainder-=result->month;
2566-
day_remainder-=result->day;
2570+
result->month= (int32) (span->month /factor);
2571+
result->day= (int32) (span->day /factor);
25672572

25682573
/*
2569-
* Handle any fractional parts the same way as in interval_mul.
2574+
*Fractional months full days into days. See comment in
2575+
*interval_mul().
25702576
*/
2577+
month_remainder_days= (orig_month* (double)DAYS_PER_MONTH) /factor-
2578+
result->month* (double)DAYS_PER_MONTH;
2579+
sec_remainder= (orig_day* (double)SECS_PER_DAY) /factor-
2580+
result->day* (double)SECS_PER_DAY+
2581+
(month_remainder_days- (int32)month_remainder_days)*SECS_PER_DAY;
25712582

2572-
/* fractional months full days into days */
2573-
month_remainder_days=month_remainder*DAYS_PER_MONTH;
2583+
/* cascade units down */
25742584
result->day+= (int32)month_remainder_days;
2575-
/* fractional months partial days into time */
2576-
day_remainder+=month_remainder_days- (int32)month_remainder_days;
2577-
25782585
#ifdefHAVE_INT64_TIMESTAMP
2579-
result->time=rint(span->time /factor+day_remainder*USECS_PER_DAY);
2586+
result->time=rint(span->time /factor+sec_remainder*USECS_PER_SEC);
25802587
#else
2581-
result->time=span->time /factor+day_remainder*SECS_PER_DAY;
2588+
/* See TSROUND comment in interval_mul(). */
2589+
result->time=span->time /factor+TSROUND(sec_remainder);
25822590
#endif
25832591

25842592
PG_RETURN_INTERVAL_P(result);

‎src/test/regress/expected/interval.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -218,7 +218,7 @@ SELECT '' AS ten, * FROM INTERVAL_TBL;
218218
select avg(f1) from interval_tbl;
219219
avg
220220
-------------------------------------------------
221-
@ 4 years 1 mon9 days28 hours 18 mins 23 secs
221+
@ 4 years 1 mon10 days4 hours 18 mins 23 secs
222222
(1 row)
223223

224224
-- test long interval input

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp