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

Commitd85ce01

Browse files
committed
Improve handling of date_trunc() units for infinite input values
Previously, if an infinite value was passed to date_trunc(), then thesame infinite value would always be returned regardless of the fieldunit given by the caller. This commit updates the function so that anerror is returned when an invalid unit is passed to date_trunc() with aninfinite value.This matches the behavior of date_trunc() with a finite value anddate_part() with an infinite value, making the handling of interval,timestamp and timestamptz more consistent across the board for these twofunctions.Some tests are added to cover all these new failure cases, with anunsupported unit and infinite values for the three data types. Therewere no test cases in core that checked all these patterns up to now.Author: Joseph KoshakowDiscussion:https://postgr.es/m/CAAvxfHc4084dGzEJR0_pBZkDuqbPGc5wn7gK_M0XR_kRiCdUJQ@mail.gmail.com
1 parent61cac71 commitd85ce01

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
structpg_tmtt,
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+
caseDTK_WEEK:
4641+
caseDTK_MILLENNIUM:
4642+
caseDTK_CENTURY:
4643+
caseDTK_DECADE:
4644+
caseDTK_YEAR:
4645+
caseDTK_QUARTER:
4646+
caseDTK_MONTH:
4647+
caseDTK_DAY:
4648+
caseDTK_HOUR:
4649+
caseDTK_MINUTE:
4650+
caseDTK_SECOND:
4651+
caseDTK_MILLISEC:
4652+
caseDTK_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+
caseDTK_WEEK:
4879+
caseDTK_MILLENNIUM:
4880+
caseDTK_CENTURY:
4881+
caseDTK_DECADE:
4882+
caseDTK_YEAR:
4883+
caseDTK_QUARTER:
4884+
caseDTK_MONTH:
4885+
caseDTK_DAY:
4886+
caseDTK_HOUR:
4887+
caseDTK_MINUTE:
4888+
caseDTK_SECOND:
4889+
caseDTK_MILLISEC:
4890+
caseDTK_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
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(1);
49675031
TimestampTzresult;
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
TimestampTzresult;
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+
caseDTK_MILLENNIUM:
5095+
caseDTK_CENTURY:
5096+
caseDTK_DECADE:
5097+
caseDTK_YEAR:
5098+
caseDTK_QUARTER:
5099+
caseDTK_MONTH:
5100+
caseDTK_DAY:
5101+
caseDTK_HOUR:
5102+
caseDTK_MINUTE:
5103+
caseDTK_SECOND:
5104+
caseDTK_MILLISEC:
5105+
caseDTK_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 iAS interval, date_trunc('week', i)
780+
FROM INFINITE_INTERVAL_TBL
781+
WHERE NOT isfinite(i);
782+
783+
SELECT iAS interval, date_trunc('ago', i)
784+
FROM INFINITE_INTERVAL_TBL
785+
WHERE NOT isfinite(i);
786+
779787
SELECT iAS 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_TBLWHERE 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)
[8]ページ先頭
©2009-2025 Movatter.jp