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

Commit647d87c

Browse files
committed
Make extract() do something more reasonable with infinite datetimes.
Historically, extract() just returned zero for any case involving aninfinite timestamp[tz] input; even cases in which the unit name wasinvalid. This is not very sensible. Instead, return infinity or-infinity as appropriate when the requested field is one that ismonotonically increasing (e.g, year, epoch), or NULL when it is not(e.g., day, hour). Also, throw the expected errors for bad unit names.BACKWARDS INCOMPATIBLE CHANGEVitaly Burovoy, reviewed by Vik Fearing
1 parentd9b9289 commit647d87c

File tree

4 files changed

+405
-12
lines changed

4 files changed

+405
-12
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7521,6 +7521,17 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
75217521
</variablelist>
75227522
</para>
75237523

7524+
<note>
7525+
<para>
7526+
When the input value is +/-Infinity, <function>extract</> returns
7527+
+/-Infinity for monotonically-increasing fields (<literal>epoch</>,
7528+
<literal>julian</>, <literal>year</>, <literal>isoyear</>,
7529+
<literal>decade</>, <literal>century</>, and <literal>millennium</>).
7530+
For other fields, NULL is returned. <productname>PostgreSQL</>
7531+
versions before 9.6 returned zero for all cases of infinite input.
7532+
</para>
7533+
</note>
7534+
75247535
<para>
75257536
The <function>extract</function> function is primarily intended
75267537
for computational processing. For formatting date/time values for

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

Lines changed: 99 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -4311,6 +4311,83 @@ date2isoyearday(int year, int mon, int mday)
43114311
returndate2j(year,mon,mday)-isoweek2j(date2isoyear(year,mon,mday),1)+1;
43124312
}
43134313

4314+
/*
4315+
* NonFiniteTimestampTzPart
4316+
*
4317+
*Used by timestamp_part and timestamptz_part when extracting from infinite
4318+
*timestamp[tz]. Returns +/-Infinity if that is the appropriate result,
4319+
*otherwise returns zero (which should be taken as meaning to return NULL).
4320+
*
4321+
*Errors thrown here for invalid units should exactly match those that
4322+
*would be thrown in the calling functions, else there will be unexpected
4323+
*discrepancies between finite- and infinite-input cases.
4324+
*/
4325+
staticfloat8
4326+
NonFiniteTimestampTzPart(inttype,intunit,char*lowunits,
4327+
boolisNegative,boolisTz)
4328+
{
4329+
if ((type!=UNITS)&& (type!=RESERV))
4330+
{
4331+
if (isTz)
4332+
ereport(ERROR,
4333+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4334+
errmsg("timestamp with time zone units \"%s\" not recognized",
4335+
lowunits)));
4336+
else
4337+
ereport(ERROR,
4338+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4339+
errmsg("timestamp units \"%s\" not recognized",
4340+
lowunits)));
4341+
}
4342+
4343+
switch (unit)
4344+
{
4345+
/* Oscillating units */
4346+
caseDTK_MICROSEC:
4347+
caseDTK_MILLISEC:
4348+
caseDTK_SECOND:
4349+
caseDTK_MINUTE:
4350+
caseDTK_HOUR:
4351+
caseDTK_DAY:
4352+
caseDTK_MONTH:
4353+
caseDTK_QUARTER:
4354+
caseDTK_WEEK:
4355+
caseDTK_DOW:
4356+
caseDTK_ISODOW:
4357+
caseDTK_DOY:
4358+
caseDTK_TZ:
4359+
caseDTK_TZ_MINUTE:
4360+
caseDTK_TZ_HOUR:
4361+
return0.0;
4362+
4363+
/* Monotonically-increasing units */
4364+
caseDTK_YEAR:
4365+
caseDTK_DECADE:
4366+
caseDTK_CENTURY:
4367+
caseDTK_MILLENNIUM:
4368+
caseDTK_JULIAN:
4369+
caseDTK_ISOYEAR:
4370+
caseDTK_EPOCH:
4371+
if (isNegative)
4372+
return-get_float8_infinity();
4373+
else
4374+
returnget_float8_infinity();
4375+
4376+
default:
4377+
if (isTz)
4378+
ereport(ERROR,
4379+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4380+
errmsg("timestamp with time zone units \"%s\" not supported",
4381+
lowunits)));
4382+
else
4383+
ereport(ERROR,
4384+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4385+
errmsg("timestamp units \"%s\" not supported",
4386+
lowunits)));
4387+
return0.0;/* keep compiler quiet */
4388+
}
4389+
}
4390+
43144391
/* timestamp_part()
43154392
* Extract specified field from timestamp.
43164393
*/
@@ -4327,12 +4404,6 @@ timestamp_part(PG_FUNCTION_ARGS)
43274404
structpg_tmtt,
43284405
*tm=&tt;
43294406

4330-
if (TIMESTAMP_NOT_FINITE(timestamp))
4331-
{
4332-
result=0;
4333-
PG_RETURN_FLOAT8(result);
4334-
}
4335-
43364407
lowunits=downcase_truncate_identifier(VARDATA_ANY(units),
43374408
VARSIZE_ANY_EXHDR(units),
43384409
false);
@@ -4341,6 +4412,17 @@ timestamp_part(PG_FUNCTION_ARGS)
43414412
if (type==UNKNOWN_FIELD)
43424413
type=DecodeSpecial(0,lowunits,&val);
43434414

4415+
if (TIMESTAMP_NOT_FINITE(timestamp))
4416+
{
4417+
result=NonFiniteTimestampTzPart(type,val,lowunits,
4418+
TIMESTAMP_IS_NOBEGIN(timestamp),
4419+
false);
4420+
if (result)
4421+
PG_RETURN_FLOAT8(result);
4422+
else
4423+
PG_RETURN_NULL();
4424+
}
4425+
43444426
if (type==UNITS)
43454427
{
43464428
if (timestamp2tm(timestamp,NULL,tm,&fsec,NULL,NULL)!=0)
@@ -4538,12 +4620,6 @@ timestamptz_part(PG_FUNCTION_ARGS)
45384620
structpg_tmtt,
45394621
*tm=&tt;
45404622

4541-
if (TIMESTAMP_NOT_FINITE(timestamp))
4542-
{
4543-
result=0;
4544-
PG_RETURN_FLOAT8(result);
4545-
}
4546-
45474623
lowunits=downcase_truncate_identifier(VARDATA_ANY(units),
45484624
VARSIZE_ANY_EXHDR(units),
45494625
false);
@@ -4552,6 +4628,17 @@ timestamptz_part(PG_FUNCTION_ARGS)
45524628
if (type==UNKNOWN_FIELD)
45534629
type=DecodeSpecial(0,lowunits,&val);
45544630

4631+
if (TIMESTAMP_NOT_FINITE(timestamp))
4632+
{
4633+
result=NonFiniteTimestampTzPart(type,val,lowunits,
4634+
TIMESTAMP_IS_NOBEGIN(timestamp),
4635+
true);
4636+
if (result)
4637+
PG_RETURN_FLOAT8(result);
4638+
else
4639+
PG_RETURN_NULL();
4640+
}
4641+
45554642
if (type==UNITS)
45564643
{
45574644
if (timestamp2tm(timestamp,&tz,tm,&fsec,NULL,NULL)!=0)

0 commit comments

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