Skip to content

Commit d85ce01

Browse files
michaelpqmichaelpq
committed
Improve handling of date_trunc() units for infinite input values
Previously, if an infinite value was passed to date_trunc(), then the same infinite value would always be returned regardless of the field unit given by the caller. This commit updates the function so that an error is returned when an invalid unit is passed to date_trunc() with an infinite value. This matches the behavior of date_trunc() with a finite value and date_part() with an infinite value, making the handling of interval, timestamp and timestamptz more consistent across the board for these two functions. Some tests are added to cover all these new failure cases, with an unsupported unit and infinite values for the three data types. There were no test cases in core that checked all these patterns up to now. Author: Joseph Koshakow Discussion: https://postgr.es/m/CAAvxfHc4084dGzEJR0_pBZkDuqbPGc5wn7gK_M0XR_kRiCdUJQ@mail.gmail.com
1 parent 61cac71 commit d85ce01

File tree

7 files changed

+131
-19
lines changed

7 files changed

+131
-19
lines changed

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

Lines changed: 102 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -4620,9 +4620,6 @@ timestamp_trunc(PG_FUNCTION_ARGS)
46204620
struct pg_tm tt,
46214621
*tm = &tt;
46224622

4623-
if (TIMESTAMP_NOT_FINITE(timestamp))
4624-
PG_RETURN_TIMESTAMP(timestamp);
4625-
46264623
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
46274624
VARSIZE_ANY_EXHDR(units),
46284625
false);
@@ -4631,6 +4628,39 @@ timestamp_trunc(PG_FUNCTION_ARGS)
46314628

46324629
if (type == UNITS)
46334630
{
4631+
if (TIMESTAMP_NOT_FINITE(timestamp))
4632+
{
4633+
/*
4634+
* Errors thrown here for invalid units should exactly match those
4635+
* below, else there will be unexpected discrepancies between
4636+
* finite- and infinite-input cases.
4637+
*/
4638+
switch (val)
4639+
{
4640+
case DTK_WEEK:
4641+
case DTK_MILLENNIUM:
4642+
case DTK_CENTURY:
4643+
case DTK_DECADE:
4644+
case DTK_YEAR:
4645+
case DTK_QUARTER:
4646+
case DTK_MONTH:
4647+
case DTK_DAY:
4648+
case DTK_HOUR:
4649+
case DTK_MINUTE:
4650+
case DTK_SECOND:
4651+
case DTK_MILLISEC:
4652+
case DTK_MICROSEC:
4653+
PG_RETURN_TIMESTAMP(timestamp);
4654+
break;
4655+
default:
4656+
ereport(ERROR,
4657+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4658+
errmsg("unit \"%s\" not supported for type %s",
4659+
lowunits, format_type_be(TIMESTAMPOID))));
4660+
result = 0;
4661+
}
4662+
}
4663+
46344664
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
46354665
ereport(ERROR,
46364666
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -4836,6 +4866,40 @@ timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
48364866

48374867
if (type == UNITS)
48384868
{
4869+
if (TIMESTAMP_NOT_FINITE(timestamp))
4870+
{
4871+
/*
4872+
* Errors thrown here for invalid units should exactly match those
4873+
* below, else there will be unexpected discrepancies between
4874+
* finite- and infinite-input cases.
4875+
*/
4876+
switch (val)
4877+
{
4878+
case DTK_WEEK:
4879+
case DTK_MILLENNIUM:
4880+
case DTK_CENTURY:
4881+
case DTK_DECADE:
4882+
case DTK_YEAR:
4883+
case DTK_QUARTER:
4884+
case DTK_MONTH:
4885+
case DTK_DAY:
4886+
case DTK_HOUR:
4887+
case DTK_MINUTE:
4888+
case DTK_SECOND:
4889+
case DTK_MILLISEC:
4890+
case DTK_MICROSEC:
4891+
PG_RETURN_TIMESTAMPTZ(timestamp);
4892+
break;
4893+
4894+
default:
4895+
ereport(ERROR,
4896+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4897+
errmsg("unit \"%s\" not supported for type %s",
4898+
lowunits, format_type_be(TIMESTAMPTZOID))));
4899+
result = 0;
4900+
}
4901+
}
4902+
48394903
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
48404904
ereport(ERROR,
48414905
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -4966,9 +5030,6 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
49665030
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
49675031
TimestampTz result;
49685032

4969-
if (TIMESTAMP_NOT_FINITE(timestamp))
4970-
PG_RETURN_TIMESTAMPTZ(timestamp);
4971-
49725033
result = timestamptz_trunc_internal(units, timestamp, session_timezone);
49735034

49745035
PG_RETURN_TIMESTAMPTZ(result);
@@ -4986,13 +5047,6 @@ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
49865047
TimestampTz result;
49875048
pg_tz *tzp;
49885049

4989-
/*
4990-
* timestamptz_zone() doesn't look up the zone for infinite inputs, so we
4991-
* don't do so here either.
4992-
*/
4993-
if (TIMESTAMP_NOT_FINITE(timestamp))
4994-
PG_RETURN_TIMESTAMP(timestamp);
4995-
49965050
/*
49975051
* Look up the requested timezone.
49985052
*/
@@ -5020,12 +5074,6 @@ interval_trunc(PG_FUNCTION_ARGS)
50205074

50215075
result = (Interval *) palloc(sizeof(Interval));
50225076

5023-
if (INTERVAL_NOT_FINITE(interval))
5024-
{
5025-
memcpy(result, interval, sizeof(Interval));
5026-
PG_RETURN_INTERVAL_P(result);
5027-
}
5028-
50295077
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
50305078
VARSIZE_ANY_EXHDR(units),
50315079
false);
@@ -5034,6 +5082,41 @@ interval_trunc(PG_FUNCTION_ARGS)
50345082

50355083
if (type == UNITS)
50365084
{
5085+
if (INTERVAL_NOT_FINITE(interval))
5086+
{
5087+
/*
5088+
* Errors thrown here for invalid units should exactly match those
5089+
* below, else there will be unexpected discrepancies between
5090+
* finite- and infinite-input cases.
5091+
*/
5092+
switch (val)
5093+
{
5094+
case DTK_MILLENNIUM:
5095+
case DTK_CENTURY:
5096+
case DTK_DECADE:
5097+
case DTK_YEAR:
5098+
case DTK_QUARTER:
5099+
case DTK_MONTH:
5100+
case DTK_DAY:
5101+
case DTK_HOUR:
5102+
case DTK_MINUTE:
5103+
case DTK_SECOND:
5104+
case DTK_MILLISEC:
5105+
case DTK_MICROSEC:
5106+
memcpy(result, interval, sizeof(Interval));
5107+
PG_RETURN_INTERVAL_P(result);
5108+
break;
5109+
5110+
default:
5111+
ereport(ERROR,
5112+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5113+
errmsg("unit \"%s\" not supported for type %s",
5114+
lowunits, format_type_be(INTERVALOID)),
5115+
(val == DTK_WEEK) ? errdetail("Months usually have fractional weeks.") : 0));
5116+
result = 0;
5117+
}
5118+
}
5119+
50375120
interval2itm(*interval, tm);
50385121
switch (val)
50395122
{

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2219,6 +2219,15 @@ SELECT i AS interval, date_trunc('hour', i)
22192219
-infinity | -infinity
22202220
(2 rows)
22212221

2222+
SELECT i AS interval, date_trunc('week', i)
2223+
FROM INFINITE_INTERVAL_TBL
2224+
WHERE NOT isfinite(i);
2225+
ERROR: unit "week" not supported for type interval
2226+
DETAIL: Months usually have fractional weeks.
2227+
SELECT i AS interval, date_trunc('ago', i)
2228+
FROM INFINITE_INTERVAL_TBL
2229+
WHERE NOT isfinite(i);
2230+
ERROR: unit "ago" not recognized for type interval
22222231
SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
22232232
FROM INFINITE_INTERVAL_TBL
22242233
WHERE NOT isfinite(i);

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -591,6 +591,8 @@ SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc
591591
Mon Feb 23 00:00:00 2004
592592
(1 row)
593593

594+
SELECT date_trunc( 'ago', timestamp 'infinity' ) AS invalid_trunc;
595+
ERROR: unit "ago" not recognized for type timestamp without time zone
594596
-- verify date_bin behaves the same as date_trunc for relevant intervals
595597
-- case 1: AD dates, origin < input
596598
SELECT

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -701,6 +701,8 @@ SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393'
701701
Mon Feb 23 00:00:00 2004 PST
702702
(1 row)
703703

704+
SELECT date_trunc( 'ago', timestamp with time zone 'infinity' ) AS invalid_trunc;
705+
ERROR: unit "ago" not recognized for type timestamp with time zone
704706
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
705707
sydney_trunc
706708
------------------------------
@@ -719,6 +721,8 @@ SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET
719721
Thu Feb 15 20:00:00 2001 PST
720722
(1 row)
721723

724+
SELECT date_trunc('ago', timestamp with time zone 'infinity', 'GMT') AS invalid_zone_trunc;
725+
ERROR: unit "ago" not recognized for type timestamp with time zone
722726
-- verify date_bin behaves the same as date_trunc for relevant intervals
723727
SELECT
724728
str,

‎src/test/regress/sql/interval.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -776,6 +776,14 @@ SELECT i AS interval, date_trunc('hour', i)
776776
FROM INFINITE_INTERVAL_TBL
777777
WHERE NOT isfinite(i);
778778

779+
SELECT i AS interval, date_trunc('week', i)
780+
FROM INFINITE_INTERVAL_TBL
781+
WHERE NOT isfinite(i);
782+
783+
SELECT i AS interval, date_trunc('ago', i)
784+
FROM INFINITE_INTERVAL_TBL
785+
WHERE NOT isfinite(i);
786+
779787
SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
780788
FROM INFINITE_INTERVAL_TBL
781789
WHERE NOT isfinite(i);

‎src/test/regress/sql/timestamp.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -176,6 +176,8 @@ SELECT d1 - timestamp without time zone '1997-01-02' AS diff
176176

177177
SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
178178

179+
SELECT date_trunc( 'ago', timestamp 'infinity' ) AS invalid_trunc;
180+
179181
-- verify date_bin behaves the same as date_trunc for relevant intervals
180182

181183
-- case 1: AD dates, origin < input

‎src/test/regress/sql/timestamptz.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,10 +200,14 @@ SELECT d1 - timestamp with time zone '1997-01-02' AS diff
200200
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
201201

202202
SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
203+
SELECT date_trunc( 'ago', timestamp with time zone 'infinity' ) AS invalid_trunc;
203204

204205
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
205206
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
206207
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
208+
SELECT date_trunc('ago', timestamp with time zone 'infinity', 'GMT') AS invalid_zone_trunc;
209+
210+
207211

208212
-- verify date_bin behaves the same as date_trunc for relevant intervals
209213
SELECT

0 commit comments

Comments
 (0)