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

Commit66c74f8

Browse files
committed
Implement parse_datetime() function
This commit adds parse_datetime() function, which implements datetimeparsing with extended features demanded by upcoming jsonpath .datetime()method: * Dynamic type identification based on template string, * Support for standard-conforming 'strict' mode, * Timezone offset is returned as separate value.Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov.Revised by me.Discussion:https://postgr.es/m/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.comDiscussion:https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.comAuthor: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander KorotkovReviewed-by: Anastasia Lubennikova, Peter Eisentraut
1 parent1a950f3 commit66c74f8

File tree

4 files changed

+296
-12
lines changed

4 files changed

+296
-12
lines changed

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

Lines changed: 3 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -41,11 +41,6 @@
4141
#endif
4242

4343

44-
staticinttm2time(structpg_tm*tm,fsec_tfsec,TimeADT*result);
45-
staticinttm2timetz(structpg_tm*tm,fsec_tfsec,inttz,TimeTzADT*result);
46-
staticvoidAdjustTimeForTypmod(TimeADT*time,int32typmod);
47-
48-
4944
/* common code for timetypmodin and timetztypmodin */
5045
staticint32
5146
anytime_typmodin(boolistz,ArrayType*ta)
@@ -1203,7 +1198,7 @@ time_in(PG_FUNCTION_ARGS)
12031198
/* tm2time()
12041199
* Convert a tm structure to a time data type.
12051200
*/
1206-
staticint
1201+
int
12071202
tm2time(structpg_tm*tm,fsec_tfsec,TimeADT*result)
12081203
{
12091204
*result= ((((tm->tm_hour*MINS_PER_HOUR+tm->tm_min)*SECS_PER_MINUTE)+tm->tm_sec)
@@ -1379,7 +1374,7 @@ time_scale(PG_FUNCTION_ARGS)
13791374
* have a fundamental tie together but rather a coincidence of
13801375
* implementation. - thomas
13811376
*/
1382-
staticvoid
1377+
void
13831378
AdjustTimeForTypmod(TimeADT*time,int32typmod)
13841379
{
13851380
staticconstint64TimeScales[MAX_TIME_PRECISION+1]= {
@@ -1957,7 +1952,7 @@ time_part(PG_FUNCTION_ARGS)
19571952
/* tm2timetz()
19581953
* Convert a tm structure to a time data type.
19591954
*/
1960-
staticint
1955+
int
19611956
tm2timetz(structpg_tm*tm,fsec_tfsec,inttz,TimeTzADT*result)
19621957
{
19631958
result->time= ((((tm->tm_hour*MINS_PER_HOUR+tm->tm_min)*SECS_PER_MINUTE)+tm->tm_sec)*

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

Lines changed: 287 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -992,6 +992,11 @@ typedef struct NUMProc
992992
*L_currency_symbol;
993993
}NUMProc;
994994

995+
/* Return flags for DCH_from_char() */
996+
#defineDCH_DATED0x01
997+
#defineDCH_TIMED0x02
998+
#defineDCH_ZONED0x04
999+
9951000
/* ----------
9961001
* Functions
9971002
* ----------
@@ -1025,7 +1030,8 @@ static intfrom_char_parse_int(int *dest, char **src, FormatNode *node);
10251030
staticintseq_search(char*name,constchar*const*array,inttype,intmax,int*len);
10261031
staticintfrom_char_seq_search(int*dest,char**src,constchar*const*array,inttype,intmax,FormatNode*node);
10271032
staticvoiddo_to_timestamp(text*date_txt,text*fmt,boolstd,
1028-
structpg_tm*tm,fsec_t*fsec,int*fprec);
1033+
structpg_tm*tm,fsec_t*fsec,int*fprec,
1034+
uint32*flags);
10291035
staticchar*fill_str(char*str,intc,intmax);
10301036
staticFormatNode*NUM_cache(intlen,NUMDesc*Num,text*pars_str,bool*shouldFree);
10311037
staticchar*int_to_roman(intnumber);
@@ -3517,6 +3523,109 @@ DCH_prevent_counter_overflow(void)
35173523
}
35183524
}
35193525

3526+
/* Get mask of date/time/zone components present in format nodes. */
3527+
staticint
3528+
DCH_datetime_type(FormatNode*node)
3529+
{
3530+
FormatNode*n;
3531+
intflags=0;
3532+
3533+
for (n=node;n->type!=NODE_TYPE_END;n++)
3534+
{
3535+
if (n->type!=NODE_TYPE_ACTION)
3536+
continue;
3537+
3538+
switch (n->key->id)
3539+
{
3540+
caseDCH_FX:
3541+
break;
3542+
caseDCH_A_M:
3543+
caseDCH_P_M:
3544+
caseDCH_a_m:
3545+
caseDCH_p_m:
3546+
caseDCH_AM:
3547+
caseDCH_PM:
3548+
caseDCH_am:
3549+
caseDCH_pm:
3550+
caseDCH_HH:
3551+
caseDCH_HH12:
3552+
caseDCH_HH24:
3553+
caseDCH_MI:
3554+
caseDCH_SS:
3555+
caseDCH_MS:/* millisecond */
3556+
caseDCH_US:/* microsecond */
3557+
caseDCH_FF1:
3558+
caseDCH_FF2:
3559+
caseDCH_FF3:
3560+
caseDCH_FF4:
3561+
caseDCH_FF5:
3562+
caseDCH_FF6:
3563+
caseDCH_SSSS:
3564+
flags |=DCH_TIMED;
3565+
break;
3566+
caseDCH_tz:
3567+
caseDCH_TZ:
3568+
caseDCH_OF:
3569+
ereport(ERROR,
3570+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3571+
errmsg("formatting field \"%s\" is only supported in to_char",
3572+
n->key->name)));
3573+
flags |=DCH_ZONED;
3574+
break;
3575+
caseDCH_TZH:
3576+
caseDCH_TZM:
3577+
flags |=DCH_ZONED;
3578+
break;
3579+
caseDCH_A_D:
3580+
caseDCH_B_C:
3581+
caseDCH_a_d:
3582+
caseDCH_b_c:
3583+
caseDCH_AD:
3584+
caseDCH_BC:
3585+
caseDCH_ad:
3586+
caseDCH_bc:
3587+
caseDCH_MONTH:
3588+
caseDCH_Month:
3589+
caseDCH_month:
3590+
caseDCH_MON:
3591+
caseDCH_Mon:
3592+
caseDCH_mon:
3593+
caseDCH_MM:
3594+
caseDCH_DAY:
3595+
caseDCH_Day:
3596+
caseDCH_day:
3597+
caseDCH_DY:
3598+
caseDCH_Dy:
3599+
caseDCH_dy:
3600+
caseDCH_DDD:
3601+
caseDCH_IDDD:
3602+
caseDCH_DD:
3603+
caseDCH_D:
3604+
caseDCH_ID:
3605+
caseDCH_WW:
3606+
caseDCH_Q:
3607+
caseDCH_CC:
3608+
caseDCH_Y_YYY:
3609+
caseDCH_YYYY:
3610+
caseDCH_IYYY:
3611+
caseDCH_YYY:
3612+
caseDCH_IYY:
3613+
caseDCH_YY:
3614+
caseDCH_IY:
3615+
caseDCH_Y:
3616+
caseDCH_I:
3617+
caseDCH_RM:
3618+
caseDCH_rm:
3619+
caseDCH_W:
3620+
caseDCH_J:
3621+
flags |=DCH_DATED;
3622+
break;
3623+
}
3624+
}
3625+
3626+
returnflags;
3627+
}
3628+
35203629
/* select a DCHCacheEntry to hold the given format picture */
35213630
staticDCHCacheEntry*
35223631
DCH_cache_getnew(constchar*str,boolstd)
@@ -3808,7 +3917,7 @@ to_timestamp(PG_FUNCTION_ARGS)
38083917
fsec_tfsec;
38093918
intfprec;
38103919

3811-
do_to_timestamp(date_txt,fmt, false,&tm,&fsec,&fprec);
3920+
do_to_timestamp(date_txt,fmt, false,&tm,&fsec,&fprec,NULL);
38123921

38133922
/* Use the specified time zone, if any. */
38143923
if (tm.tm_zone)
@@ -3847,7 +3956,7 @@ to_date(PG_FUNCTION_ARGS)
38473956
structpg_tmtm;
38483957
fsec_tfsec;
38493958

3850-
do_to_timestamp(date_txt,fmt, false,&tm,&fsec,NULL);
3959+
do_to_timestamp(date_txt,fmt, false,&tm,&fsec,NULL,NULL);
38513960

38523961
/* Prevent overflow in Julian-day routines */
38533962
if (!IS_VALID_JULIAN(tm.tm_year,tm.tm_mon,tm.tm_mday))
@@ -3868,6 +3977,176 @@ to_date(PG_FUNCTION_ARGS)
38683977
PG_RETURN_DATEADT(result);
38693978
}
38703979

3980+
/*
3981+
* Convert the 'date_txt' input to a datetime type using argument 'fmt' as a format string.
3982+
* The actual data type (returned in 'typid', 'typmod') is determined by
3983+
* the presence of date/time/zone components in the format string.
3984+
*
3985+
* When timezone component is present, the corresponding offset is set to '*tz'.
3986+
*/
3987+
Datum
3988+
parse_datetime(text*date_txt,text*fmt,boolstrict,Oid*typid,
3989+
int32*typmod,int*tz)
3990+
{
3991+
structpg_tmtm;
3992+
fsec_tfsec;
3993+
intfprec=0;
3994+
uint32flags;
3995+
3996+
do_to_timestamp(date_txt,fmt,strict,&tm,&fsec,&fprec,&flags);
3997+
3998+
*typmod=fprec ?fprec :-1;/* fractional part precision */
3999+
4000+
if (flags&DCH_DATED)
4001+
{
4002+
if (flags&DCH_TIMED)
4003+
{
4004+
if (flags&DCH_ZONED)
4005+
{
4006+
TimestampTzresult;
4007+
4008+
if (tm.tm_zone)
4009+
{
4010+
intdterr=DecodeTimezone(unconstify(char*,tm.tm_zone),tz);
4011+
4012+
if (dterr)
4013+
DateTimeParseError(dterr,text_to_cstring(date_txt),"timestamptz");
4014+
}
4015+
else
4016+
{
4017+
/*
4018+
* Time zone is present in format string, but not in input
4019+
* string. Assuming do_to_timestamp() triggers no error
4020+
* this should be possible only in non-strict case.
4021+
*/
4022+
Assert(!strict);
4023+
4024+
ereport(ERROR,
4025+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
4026+
errmsg("missing time zone in input string for type timestamptz")));
4027+
}
4028+
4029+
if (tm2timestamp(&tm,fsec,tz,&result)!=0)
4030+
ereport(ERROR,
4031+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4032+
errmsg("timestamptz out of range")));
4033+
4034+
AdjustTimestampForTypmod(&result,*typmod);
4035+
4036+
*typid=TIMESTAMPTZOID;
4037+
returnTimestampTzGetDatum(result);
4038+
}
4039+
else
4040+
{
4041+
Timestampresult;
4042+
4043+
if (tm2timestamp(&tm,fsec,NULL,&result)!=0)
4044+
ereport(ERROR,
4045+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4046+
errmsg("timestamp out of range")));
4047+
4048+
AdjustTimestampForTypmod(&result,*typmod);
4049+
4050+
*typid=TIMESTAMPOID;
4051+
returnTimestampGetDatum(result);
4052+
}
4053+
}
4054+
else
4055+
{
4056+
if (flags&DCH_ZONED)
4057+
{
4058+
ereport(ERROR,
4059+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
4060+
errmsg("datetime format is zoned but not timed")));
4061+
}
4062+
else
4063+
{
4064+
DateADTresult;
4065+
4066+
/* Prevent overflow in Julian-day routines */
4067+
if (!IS_VALID_JULIAN(tm.tm_year,tm.tm_mon,tm.tm_mday))
4068+
ereport(ERROR,
4069+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4070+
errmsg("date out of range: \"%s\"",
4071+
text_to_cstring(date_txt))));
4072+
4073+
result=date2j(tm.tm_year,tm.tm_mon,tm.tm_mday)-
4074+
POSTGRES_EPOCH_JDATE;
4075+
4076+
/* Now check for just-out-of-range dates */
4077+
if (!IS_VALID_DATE(result))
4078+
ereport(ERROR,
4079+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4080+
errmsg("date out of range: \"%s\"",
4081+
text_to_cstring(date_txt))));
4082+
4083+
*typid=DATEOID;
4084+
returnDateADTGetDatum(result);
4085+
}
4086+
}
4087+
}
4088+
elseif (flags&DCH_TIMED)
4089+
{
4090+
if (flags&DCH_ZONED)
4091+
{
4092+
TimeTzADT*result=palloc(sizeof(TimeTzADT));
4093+
4094+
if (tm.tm_zone)
4095+
{
4096+
intdterr=DecodeTimezone(unconstify(char*,tm.tm_zone),tz);
4097+
4098+
if (dterr)
4099+
DateTimeParseError(dterr,text_to_cstring(date_txt),"timetz");
4100+
}
4101+
else
4102+
{
4103+
/*
4104+
* Time zone is present in format string, but not in input
4105+
* string. Assuming do_to_timestamp() triggers no error this
4106+
* should be possible only in non-strict case.
4107+
*/
4108+
Assert(!strict);
4109+
4110+
ereport(ERROR,
4111+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
4112+
errmsg("missing time zone in input string for type timetz")));
4113+
}
4114+
4115+
if (tm2timetz(&tm,fsec,*tz,result)!=0)
4116+
ereport(ERROR,
4117+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4118+
errmsg("timetz out of range")));
4119+
4120+
AdjustTimeForTypmod(&result->time,*typmod);
4121+
4122+
*typid=TIMETZOID;
4123+
returnTimeTzADTPGetDatum(result);
4124+
}
4125+
else
4126+
{
4127+
TimeADTresult;
4128+
4129+
if (tm2time(&tm,fsec,&result)!=0)
4130+
ereport(ERROR,
4131+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4132+
errmsg("time out of range")));
4133+
4134+
AdjustTimeForTypmod(&result,*typmod);
4135+
4136+
*typid=TIMEOID;
4137+
returnTimeADTGetDatum(result);
4138+
}
4139+
}
4140+
else
4141+
{
4142+
ereport(ERROR,
4143+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
4144+
errmsg("datetime format is not dated and not timed")));
4145+
}
4146+
4147+
return (Datum)0;
4148+
}
4149+
38714150
/*
38724151
* do_to_timestamp: shared code for to_timestamp and to_date
38734152
*
@@ -3883,7 +4162,8 @@ to_date(PG_FUNCTION_ARGS)
38834162
*/
38844163
staticvoid
38854164
do_to_timestamp(text*date_txt,text*fmt,boolstd,
3886-
structpg_tm*tm,fsec_t*fsec,int*fprec)
4165+
structpg_tm*tm,fsec_t*fsec,int*fprec,
4166+
uint32*flags)
38874167
{
38884168
FormatNode*format;
38894169
TmFromChartmfc;
@@ -3940,6 +4220,9 @@ do_to_timestamp(text *date_txt, text *fmt, bool std,
39404220

39414221
pfree(fmt_str);
39424222

4223+
if (flags)
4224+
*flags=DCH_datetime_type(format);
4225+
39434226
if (!incache)
39444227
pfree(format);
39454228
}

‎src/include/utils/date.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,5 +76,8 @@ extern TimeTzADT *GetSQLCurrentTime(int32 typmod);
7676
externTimeADTGetSQLLocalTime(int32typmod);
7777
externinttime2tm(TimeADTtime,structpg_tm*tm,fsec_t*fsec);
7878
externinttimetz2tm(TimeTzADT*time,structpg_tm*tm,fsec_t*fsec,int*tzp);
79+
externinttm2time(structpg_tm*tm,fsec_tfsec,TimeADT*result);
80+
externinttm2timetz(structpg_tm*tm,fsec_tfsec,inttz,TimeTzADT*result);
81+
externvoidAdjustTimeForTypmod(TimeADT*time,int32typmod);
7982

8083
#endif/* DATE_H */

0 commit comments

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