From: Nikita Glukhov Date: Tue, 6 Nov 2018 16:33:13 +0300 Subject: [PATCH 1/4] Preliminary datetime infrastructure --- src/backend/utils/adt/date.c | 11 +- src/backend/utils/adt/formatting.c | 434 +++++++++++++++++++++++++++--- src/backend/utils/adt/timestamp.c | 3 +- src/include/utils/date.h | 3 + src/include/utils/datetime.h | 2 + src/include/utils/formatting.h | 3 + src/test/regress/expected/horology.out | 86 ++++++ src/test/regress/expected/timestamp.out | 15 ++ src/test/regress/expected/timestamptz.out | 15 ++ src/test/regress/sql/horology.sql | 14 + src/test/regress/sql/timestamp.sql | 8 + src/test/regress/sql/timestamptz.sql | 8 + 12 files changed, 562 insertions(+), 40 deletions(-) diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index cb6b5e55..7d5c8ac 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -40,11 +40,6 @@ #endif -static int tm2time(struct pg_tm *tm, fsec_t fsec, TimeADT *result); -static int tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result); -static void AdjustTimeForTypmod(TimeADT *time, int32 typmod); - - /* common code for timetypmodin and timetztypmodin */ static int32 anytime_typmodin(bool istz, ArrayType *ta) @@ -1210,7 +1205,7 @@ time_in(PG_FUNCTION_ARGS) /* tm2time() * Convert a tm structure to a time data type. */ -static int +int tm2time(struct pg_tm *tm, fsec_t fsec, TimeADT *result) { *result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) @@ -1376,7 +1371,7 @@ time_scale(PG_FUNCTION_ARGS) * have a fundamental tie together but rather a coincidence of * implementation. - thomas */ -static void +void AdjustTimeForTypmod(TimeADT *time, int32 typmod) { static const int64 TimeScales[MAX_TIME_PRECISION + 1] = { @@ -1954,7 +1949,7 @@ time_part(PG_FUNCTION_ARGS) /* tm2timetz() * Convert a tm structure to a time data type. */ -static int +int tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result) { result->time = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 2923afe..7cba489 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -86,6 +86,7 @@ #endif #include "catalog/pg_collation.h" +#include "catalog/pg_type.h" #include "mb/pg_wchar.h" #include "utils/builtins.h" #include "utils/date.h" @@ -436,7 +437,8 @@ typedef struct clock, /* 12 or 24 hour clock? */ tzsign, /* +1, -1 or 0 if timezone info is absent */ tzh, - tzm; + tzm, + ff; /* fractional precision */ } TmFromChar; #define ZERO_tmfc(_X) memset(_X, 0, sizeof(TmFromChar)) @@ -596,6 +598,15 @@ typedef enum DCH_Day, DCH_Dy, DCH_D, + DCH_FF1, + DCH_FF2, + DCH_FF3, + DCH_FF4, + DCH_FF5, + DCH_FF6, + DCH_FF7, + DCH_FF8, + DCH_FF9, DCH_FX, /* global suffix */ DCH_HH24, DCH_HH12, @@ -645,6 +656,15 @@ typedef enum DCH_dd, DCH_dy, DCH_d, + DCH_ff1, + DCH_ff2, + DCH_ff3, + DCH_ff4, + DCH_ff5, + DCH_ff6, + DCH_ff7, + DCH_ff8, + DCH_ff9, DCH_fx, DCH_hh24, DCH_hh12, @@ -745,7 +765,16 @@ static const KeyWord DCH_keywords[] = { {"Day", 3, DCH_Day, false, FROM_CHAR_DATE_NONE}, {"Dy", 2, DCH_Dy, false, FROM_CHAR_DATE_NONE}, {"D", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"FX", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, /* F */ + {"FF1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* F */ + {"FF2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, + {"FF3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, + {"FF4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, + {"FF5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, + {"FF6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"FF7", 3, DCH_FF7, false, FROM_CHAR_DATE_NONE}, + {"FF8", 3, DCH_FF8, false, FROM_CHAR_DATE_NONE}, + {"FF9", 3, DCH_FF9, false, FROM_CHAR_DATE_NONE}, + {"FX", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"HH24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* H */ {"HH12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, {"HH", 2, DCH_HH, true, FROM_CHAR_DATE_NONE}, @@ -794,7 +823,16 @@ static const KeyWord DCH_keywords[] = { {"dd", 2, DCH_DD, true, FROM_CHAR_DATE_GREGORIAN}, {"dy", 2, DCH_dy, false, FROM_CHAR_DATE_NONE}, {"d", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"fx", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, /* f */ + {"ff1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* F */ + {"ff2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, + {"ff3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, + {"ff4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, + {"ff5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, + {"ff6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"ff7", 3, DCH_FF7, false, FROM_CHAR_DATE_NONE}, + {"ff8", 3, DCH_FF8, false, FROM_CHAR_DATE_NONE}, + {"ff9", 3, DCH_FF9, false, FROM_CHAR_DATE_NONE}, + {"fx", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"hh24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* h */ {"hh12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, {"hh", 2, DCH_HH, true, FROM_CHAR_DATE_NONE}, @@ -895,10 +933,10 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = { -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1, - DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF, + DCH_FF1, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF, DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZH, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY, -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc, - DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi, + DCH_day, -1, DCH_ff1, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi, -1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_ssss, DCH_tz, DCH_us, -1, DCH_ww, -1, DCH_y_yyy, -1, -1, -1, -1 @@ -962,6 +1000,10 @@ typedef struct NUMProc *L_currency_symbol; } NUMProc; +/* Return flags for DCH_from_char() */ +#define DCH_DATED 0x01 +#define DCH_TIMED 0x02 +#define DCH_ZONED 0x04 /* ---------- * Functions @@ -977,7 +1019,8 @@ static void parse_format(FormatNode *node, const char *str, const KeyWord *kw, static void DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid collid); -static void DCH_from_char(FormatNode *node, char *in, TmFromChar *out); +static void DCH_from_char(FormatNode *node, char *in, TmFromChar *out, + bool strict); #ifdef DEBUG_TO_FROM_CHAR static void dump_index(const KeyWord *k, const int *index); @@ -994,8 +1037,8 @@ static int from_char_parse_int_len(int *dest, char **src, const int len, FormatN static int from_char_parse_int(int *dest, char **src, FormatNode *node); static int seq_search(char *name, const char *const *array, int type, int max, int *len); static int from_char_seq_search(int *dest, char **src, const char *const *array, int type, int max, FormatNode *node); -static void do_to_timestamp(text *date_txt, text *fmt, - struct pg_tm *tm, fsec_t *fsec); +static void do_to_timestamp(text *date_txt, const char *fmt, int fmt_len, + bool strict, struct pg_tm *tm, fsec_t *fsec, int *fprec, int *flags); static char *fill_str(char *str, int c, int max); static FormatNode *NUM_cache(int len, NUMDesc *Num, text *pars_str, bool *shouldFree); static char *int_to_roman(int number); @@ -2514,18 +2557,41 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s); break; - case DCH_MS: /* millisecond */ - sprintf(s, "%03d", (int) (in->fsec / INT64CONST(1000))); - if (S_THth(n->suffix)) - str_numth(s, s, S_TH_TYPE(n->suffix)); +#define DCH_to_char_fsec(frac_fmt, frac_val) \ + sprintf(s, frac_fmt, (int) (frac_val)); \ + if (S_THth(n->suffix)) \ + str_numth(s, s, S_TH_TYPE(n->suffix)); \ s += strlen(s); + case DCH_FF1: /* decisecond */ + DCH_to_char_fsec("%01d", in->fsec / INT64CONST(100000)); + break; + case DCH_FF2: /* centisecond */ + DCH_to_char_fsec("%02d", in->fsec / INT64CONST(10000)); + break; + case DCH_FF3: + case DCH_MS: /* millisecond */ + DCH_to_char_fsec("%03d", in->fsec / INT64CONST(1000)); break; + case DCH_FF4: + DCH_to_char_fsec("%04d", in->fsec / INT64CONST(100)); + break; + case DCH_FF5: + DCH_to_char_fsec("%05d", in->fsec / INT64CONST(10)); + break; + case DCH_FF6: case DCH_US: /* microsecond */ - sprintf(s, "%06d", (int) in->fsec); - if (S_THth(n->suffix)) - str_numth(s, s, S_TH_TYPE(n->suffix)); - s += strlen(s); + DCH_to_char_fsec("%06d", in->fsec); + break; + case DCH_FF7: + DCH_to_char_fsec("%06d0", in->fsec); break; + case DCH_FF8: + DCH_to_char_fsec("%06d00", in->fsec); + break; + case DCH_FF9: /* nanosecond */ + DCH_to_char_fsec("%06d000", in->fsec); + break; +#undef DCH_to_char_fsec case DCH_SSSS: sprintf(s, "%d", tm->tm_hour * SECS_PER_HOUR + tm->tm_min * SECS_PER_MINUTE + @@ -3007,13 +3073,15 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col /* ---------- * Process a string as denoted by a list of FormatNodes. * The TmFromChar struct pointed to by 'out' is populated with the results. + * 'strict' enables error reporting when trailing input characters or format + * nodes remain after parsing. * * Note: we currently don't have any to_interval() function, so there * is no need here for INVALID_FOR_INTERVAL checks. * ---------- */ static void -DCH_from_char(FormatNode *node, char *in, TmFromChar *out) +DCH_from_char(FormatNode *node, char *in, TmFromChar *out, bool strict) { FormatNode *n; char *s; @@ -3149,8 +3217,18 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out) SKIP_THth(s, n->suffix); break; + case DCH_FF1: + case DCH_FF2: + case DCH_FF3: + case DCH_FF4: + case DCH_FF5: + case DCH_FF6: + out->ff = n->key->id - DCH_FF1 + 1; + /* fall through */ case DCH_US: /* microsecond */ - len = from_char_parse_int_len(&out->us, &s, 6, n); + len = from_char_parse_int_len(&out->us, &s, + n->key->id == DCH_US ? 6 : + out->ff, n); out->us *= len == 1 ? 100000 : len == 2 ? 10000 : @@ -3164,6 +3242,9 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out) from_char_parse_int(&out->ssss, &s, n); SKIP_THth(s, n->suffix); break; + case DCH_FF7: + case DCH_FF8: + case DCH_FF9: case DCH_tz: case DCH_TZ: case DCH_OF: @@ -3374,6 +3455,23 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out) } } } + + if (strict) + { + if (n->type != NODE_TYPE_END) + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("input string is too short for datetime format"))); + + while (*s == ' ') + s++; + + if (*s != '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("trailing characters remain in input string after " + "datetime format"))); + } } /* @@ -3394,6 +3492,112 @@ DCH_prevent_counter_overflow(void) } } +/* Get mask of date/time/zone formatting components present in format nodes. */ +static int +DCH_datetime_type(FormatNode *node) +{ + FormatNode *n; + int flags = 0; + + for (n = node; n->type != NODE_TYPE_END; n++) + { + if (n->type != NODE_TYPE_ACTION) + continue; + + switch (n->key->id) + { + case DCH_FX: + break; + case DCH_A_M: + case DCH_P_M: + case DCH_a_m: + case DCH_p_m: + case DCH_AM: + case DCH_PM: + case DCH_am: + case DCH_pm: + case DCH_HH: + case DCH_HH12: + case DCH_HH24: + case DCH_MI: + case DCH_SS: + case DCH_MS: /* millisecond */ + case DCH_US: /* microsecond */ + case DCH_FF1: + case DCH_FF2: + case DCH_FF3: + case DCH_FF4: + case DCH_FF5: + case DCH_FF6: + case DCH_FF7: + case DCH_FF8: + case DCH_FF9: + case DCH_SSSS: + flags |= DCH_TIMED; + break; + case DCH_tz: + case DCH_TZ: + case DCH_OF: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("formatting field \"%s\" is only supported in to_char", + n->key->name))); + flags |= DCH_ZONED; + break; + case DCH_TZH: + case DCH_TZM: + flags |= DCH_ZONED; + break; + case DCH_A_D: + case DCH_B_C: + case DCH_a_d: + case DCH_b_c: + case DCH_AD: + case DCH_BC: + case DCH_ad: + case DCH_bc: + case DCH_MONTH: + case DCH_Month: + case DCH_month: + case DCH_MON: + case DCH_Mon: + case DCH_mon: + case DCH_MM: + case DCH_DAY: + case DCH_Day: + case DCH_day: + case DCH_DY: + case DCH_Dy: + case DCH_dy: + case DCH_DDD: + case DCH_IDDD: + case DCH_DD: + case DCH_D: + case DCH_ID: + case DCH_WW: + case DCH_Q: + case DCH_CC: + case DCH_Y_YYY: + case DCH_YYYY: + case DCH_IYYY: + case DCH_YYY: + case DCH_IYY: + case DCH_YY: + case DCH_IY: + case DCH_Y: + case DCH_I: + case DCH_RM: + case DCH_rm: + case DCH_W: + case DCH_J: + flags |= DCH_DATED; + break; + } + } + + return flags; +} + /* select a DCHCacheEntry to hold the given format picture */ static DCHCacheEntry * DCH_cache_getnew(const char *str) @@ -3682,8 +3886,10 @@ to_timestamp(PG_FUNCTION_ARGS) int tz; struct pg_tm tm; fsec_t fsec; + int fprec; - do_to_timestamp(date_txt, fmt, &tm, &fsec); + do_to_timestamp(date_txt, VARDATA(fmt), VARSIZE_ANY_EXHDR(fmt), false, + &tm, &fsec, &fprec, NULL); /* Use the specified time zone, if any. */ if (tm.tm_zone) @@ -3701,6 +3907,10 @@ to_timestamp(PG_FUNCTION_ARGS) (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + /* Use the specified fractional precision, if any. */ + if (fprec) + AdjustTimestampForTypmod(&result, fprec); + PG_RETURN_TIMESTAMP(result); } @@ -3718,7 +3928,8 @@ to_date(PG_FUNCTION_ARGS) struct pg_tm tm; fsec_t fsec; - do_to_timestamp(date_txt, fmt, &tm, &fsec); + do_to_timestamp(date_txt, VARDATA(fmt), VARSIZE_ANY_EXHDR(fmt), false, + &tm, &fsec, NULL, NULL); /* Prevent overflow in Julian-day routines */ if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday)) @@ -3740,10 +3951,160 @@ to_date(PG_FUNCTION_ARGS) } /* + * Make datetime type from 'date_txt' which is formated at argument 'fmt'. + * Actual datatype (returned in 'typid', 'typmod') is determined by + * presence of date/time/zone components in the format string. + */ +Datum +to_datetime(text *date_txt, const char *fmt, int fmt_len, bool strict, + Oid *typid, int32 *typmod) +{ + struct pg_tm tm; + fsec_t fsec; + int fprec = 0; + int flags; + + do_to_timestamp(date_txt, fmt, fmt_len, strict, &tm, &fsec, &fprec, &flags); + + *typmod = fprec ? fprec : -1; /* fractional part precision */ + + if (flags & DCH_DATED) + { + if (flags & DCH_TIMED) + { + if (flags & DCH_ZONED) + { + TimestampTz result; + int tz; + + if (tm.tm_zone) + { + int dterr = DecodeTimezone((char *) tm.tm_zone, &tz); + + if (dterr) + DateTimeParseError(dterr, text_to_cstring(date_txt), + "timestamptz"); + } + else + tz = DetermineTimeZoneOffset(&tm, session_timezone); + + if (tm2timestamp(&tm, fsec, &tz, &result) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamptz out of range"))); + + AdjustTimestampForTypmod(&result, *typmod); + + *typid = TIMESTAMPTZOID; + return TimestampTzGetDatum(result); + } + else + { + Timestamp result; + + if (tm2timestamp(&tm, fsec, NULL, &result) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + + AdjustTimestampForTypmod(&result, *typmod); + + *typid = TIMESTAMPOID; + return TimestampGetDatum(result); + } + } + else + { + if (flags & DCH_ZONED) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("datetime format is zoned but not timed"))); + } + else + { + DateADT result; + + /* Prevent overflow in Julian-day routines */ + if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("date out of range: \"%s\"", + text_to_cstring(date_txt)))); + + result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - + POSTGRES_EPOCH_JDATE; + + /* Now check for just-out-of-range dates */ + if (!IS_VALID_DATE(result)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("date out of range: \"%s\"", + text_to_cstring(date_txt)))); + + *typid = DATEOID; + return DateADTGetDatum(result); + } + } + } + else if (flags & DCH_TIMED) + { + if (flags & DCH_ZONED) + { + TimeTzADT *result = palloc(sizeof(TimeTzADT)); + int tz; + + if (tm.tm_zone) + { + int dterr = DecodeTimezone((char *) tm.tm_zone, &tz); + + if (dterr) + DateTimeParseError(dterr, text_to_cstring(date_txt), + "timetz"); + } + else + tz = DetermineTimeZoneOffset(&tm, session_timezone); + + if (tm2timetz(&tm, fsec, tz, result) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timetz out of range"))); + + AdjustTimeForTypmod(&result->time, *typmod); + + *typid = TIMETZOID; + return TimeTzADTPGetDatum(result); + } + else + { + TimeADT result; + + if (tm2time(&tm, fsec, &result) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("time out of range"))); + + AdjustTimeForTypmod(&result, *typmod); + + *typid = TIMEOID; + return TimeADTGetDatum(result); + } + } + else + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("datetime format is not dated and not timed"))); + } + + return (Datum) 0; +} + +/* * do_to_timestamp: shared code for to_timestamp and to_date * * Parse the 'date_txt' according to 'fmt', return results as a struct pg_tm - * and fractional seconds. + * and fractional seconds and fractional precision. * * We parse 'fmt' into a list of FormatNodes, which is then passed to * DCH_from_char to populate a TmFromChar with the parsed contents of @@ -3751,14 +4112,20 @@ to_date(PG_FUNCTION_ARGS) * * The TmFromChar is then analysed and converted into the final results in * struct 'tm' and 'fsec'. + * + * Bit mask of date/time/zone formatting components found in 'fmt_str' is + * returned in 'flags'. + * + * 'strict' enables error reporting when trailing characters remain in input or + * format strings after parsing. */ static void -do_to_timestamp(text *date_txt, text *fmt, - struct pg_tm *tm, fsec_t *fsec) +do_to_timestamp(text *date_txt, const char *fmt_str, int fmt_len, bool strict, + struct pg_tm *tm, fsec_t *fsec, int *fprec, int *flags) { FormatNode *format; TmFromChar tmfc; - int fmt_len; + char *fmt_tmp = NULL; char *date_str; int fmask; @@ -3769,15 +4136,15 @@ do_to_timestamp(text *date_txt, text *fmt, *fsec = 0; fmask = 0; /* bit mask for ValidateDate() */ - fmt_len = VARSIZE_ANY_EXHDR(fmt); + if (fmt_len < 0) /* zero-terminated */ + fmt_len = strlen(fmt_str); + else if (fmt_len > 0) /* not zero-terminated */ + fmt_str = fmt_tmp = pnstrdup(fmt_str, fmt_len); if (fmt_len) { - char *fmt_str; bool incache; - fmt_str = text_to_cstring(fmt); - if (fmt_len > DCH_CACHE_SIZE) { /* @@ -3807,13 +4174,18 @@ do_to_timestamp(text *date_txt, text *fmt, /* dump_index(DCH_keywords, DCH_index); */ #endif - DCH_from_char(format, date_str, &tmfc); + DCH_from_char(format, date_str, &tmfc, strict); + + if (flags) + *flags = DCH_datetime_type(format); - pfree(fmt_str); if (!incache) pfree(format); } + if (fmt_tmp) + pfree(fmt_tmp); + DEBUG_TMFC(&tmfc); /* @@ -3991,6 +4363,8 @@ do_to_timestamp(text *date_txt, text *fmt, *fsec += tmfc.ms * 1000; if (tmfc.us) *fsec += tmfc.us; + if (fprec) + *fprec = tmfc.ff; /* fractional precision, if specified */ /* Range-check date fields according to bit mask computed above */ if (fmask != 0) diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 449164a..fcc6d23 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -70,7 +70,6 @@ typedef struct static TimeOffset time2t(const int hour, const int min, const int sec, const fsec_t fsec); static Timestamp dt2local(Timestamp dt, int timezone); -static void AdjustTimestampForTypmod(Timestamp *time, int32 typmod); static void AdjustIntervalForTypmod(Interval *interval, int32 typmod); static TimestampTz timestamp2timestamptz(Timestamp timestamp); static Timestamp timestamptz2timestamp(TimestampTz timestamp); @@ -330,7 +329,7 @@ timestamp_scale(PG_FUNCTION_ARGS) * AdjustTimestampForTypmod --- round off a timestamp to suit given typmod * Works for either timestamp or timestamptz. */ -static void +void AdjustTimestampForTypmod(Timestamp *time, int32 typmod) { static const int64 TimestampScales[MAX_TIMESTAMP_PRECISION + 1] = { diff --git a/src/include/utils/date.h b/src/include/utils/date.h index eb6d2a1..10cc822 100644 --- a/src/include/utils/date.h +++ b/src/include/utils/date.h @@ -76,5 +76,8 @@ extern TimeTzADT *GetSQLCurrentTime(int32 typmod); extern TimeADT GetSQLLocalTime(int32 typmod); extern int time2tm(TimeADT time, struct pg_tm *tm, fsec_t *fsec); extern int timetz2tm(TimeTzADT *time, struct pg_tm *tm, fsec_t *fsec, int *tzp); +extern int tm2time(struct pg_tm *tm, fsec_t fsec, TimeADT *result); +extern int tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result); +extern void AdjustTimeForTypmod(TimeADT *time, int32 typmod); #endif /* DATE_H */ diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h index de9e9ad..165f0e7 100644 --- a/src/include/utils/datetime.h +++ b/src/include/utils/datetime.h @@ -338,4 +338,6 @@ extern TimeZoneAbbrevTable *ConvertTimeZoneAbbrevs(struct tzEntry *abbrevs, int n); extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl); +extern void AdjustTimestampForTypmod(Timestamp *time, int32 typmod); + #endif /* DATETIME_H */ diff --git a/src/include/utils/formatting.h b/src/include/utils/formatting.h index a9f5548..208cc00 100644 --- a/src/include/utils/formatting.h +++ b/src/include/utils/formatting.h @@ -28,4 +28,7 @@ extern char *asc_tolower(const char *buff, size_t nbytes); extern char *asc_toupper(const char *buff, size_t nbytes); extern char *asc_initcap(const char *buff, size_t nbytes); +extern Datum to_datetime(text *date_txt, const char *fmt, int fmt_len, + bool strict, Oid *typid, int32 *typmod); + #endif diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index b2b4577..f103d36 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -2786,6 +2786,92 @@ SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); Sun Dec 18 03:18:00 2011 PST (1 row) +SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+------------------------------ + 1 | Fri Nov 02 12:34:56 2018 PDT + 2 | Fri Nov 02 12:34:56 2018 PDT + 3 | Fri Nov 02 12:34:56 2018 PDT + 4 | Fri Nov 02 12:34:56 2018 PDT + 5 | Fri Nov 02 12:34:56 2018 PDT + 6 | Fri Nov 02 12:34:56 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.1', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+-------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.1 2018 PDT + 3 | Fri Nov 02 12:34:56.1 2018 PDT + 4 | Fri Nov 02 12:34:56.1 2018 PDT + 5 | Fri Nov 02 12:34:56.1 2018 PDT + 6 | Fri Nov 02 12:34:56.1 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.12', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+--------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.12 2018 PDT + 4 | Fri Nov 02 12:34:56.12 2018 PDT + 5 | Fri Nov 02 12:34:56.12 2018 PDT + 6 | Fri Nov 02 12:34:56.12 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.123', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+---------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.123 2018 PDT + 5 | Fri Nov 02 12:34:56.123 2018 PDT + 6 | Fri Nov 02 12:34:56.123 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+----------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.1234 2018 PDT + 5 | Fri Nov 02 12:34:56.1234 2018 PDT + 6 | Fri Nov 02 12:34:56.1234 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+------------------------------------ + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.1235 2018 PDT + 5 | Fri Nov 02 12:34:56.12345 2018 PDT + 6 | Fri Nov 02 12:34:56.12345 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+------------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.1235 2018 PDT + 5 | Fri Nov 02 12:34:56.12346 2018 PDT + 6 | Fri Nov 02 12:34:56.123456 2018 PDT +(6 rows) + +SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +ERROR: date/time field value out of range: "2018-11-02 12:34:56.123456789" +-- FF7, FF8, FF9 are not supported +SELECT to_timestamp('123', 'FF7'); +ERROR: formatting field "FF7" is only supported in to_char +SELECT to_timestamp('123', 'FF8'); +ERROR: formatting field "FF8" is only supported in to_char +SELECT to_timestamp('123', 'FF9'); +ERROR: formatting field "FF9" is only supported in to_char -- -- Check handling of multiple spaces in format and/or input -- diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index 4a2fabd..4742f0b 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -1597,6 +1597,21 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 2001 1 1 1 1 1 1 (65 rows) +SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 FF7 FF8 FF9 MS US') + FROM (VALUES + ('2018-11-02 12:34:56'::timestamp), + ('2018-11-02 12:34:56.78'), + ('2018-11-02 12:34:56.78901'), + ('2018-11-02 12:34:56.78901234') + ) d(d); + to_char_12 | to_char +------------+------------------------------------------------------------------- + | 0 00 000 0000 00000 000000 0000000 00000000 000000000 000 000000 + | 7 78 780 7800 78000 780000 7800000 78000000 780000000 780 780000 + | 7 78 789 7890 78901 789010 7890100 78901000 789010000 789 789010 + | 7 78 789 7890 78901 789012 7890120 78901200 789012000 789 789012 +(4 rows) + -- timestamp numeric fields constructor SELECT make_timestamp(2014,12,28,6,30,45.887); make_timestamp diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 2340f30..7d73f49 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1699,6 +1699,21 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 2001 1 1 1 1 1 1 (66 rows) +SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 FF7 FF8 FF9 MS US') + FROM (VALUES + ('2018-11-02 12:34:56'::timestamptz), + ('2018-11-02 12:34:56.78'), + ('2018-11-02 12:34:56.78901'), + ('2018-11-02 12:34:56.78901234') + ) d(d); + to_char_12 | to_char +------------+------------------------------------------------------------------- + | 0 00 000 0000 00000 000000 0000000 00000000 000000000 000 000000 + | 7 78 780 7800 78000 780000 7800000 78000000 780000000 780 780000 + | 7 78 789 7890 78901 789010 7890100 78901000 789010000 789 789010 + | 7 78 789 7890 78901 789012 7890120 78901200 789012000 789 789012 +(4 rows) + -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours SET timezone = '00:00'; SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index e356dd5..ef34323 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -402,6 +402,20 @@ SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); +SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.1', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.12', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.123', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; + +-- FF7, FF8, FF9 are not supported +SELECT to_timestamp('123', 'FF7'); +SELECT to_timestamp('123', 'FF8'); +SELECT to_timestamp('123', 'FF9'); + -- -- Check handling of multiple spaces in format and/or input -- diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index b7957cb..ada7bc1 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -228,5 +228,13 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM TIMESTAMP_TBL; +SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 FF7 FF8 FF9 MS US') + FROM (VALUES + ('2018-11-02 12:34:56'::timestamp), + ('2018-11-02 12:34:56.78'), + ('2018-11-02 12:34:56.78901'), + ('2018-11-02 12:34:56.78901234') + ) d(d); + -- timestamp numeric fields constructor SELECT make_timestamp(2014,12,28,6,30,45.887); diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index f17d153..e1ef747 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -248,6 +248,14 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM TIMESTAMPTZ_TBL; +SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 FF7 FF8 FF9 MS US') + FROM (VALUES + ('2018-11-02 12:34:56'::timestamptz), + ('2018-11-02 12:34:56.78'), + ('2018-11-02 12:34:56.78901'), + ('2018-11-02 12:34:56.78901234') + ) d(d); + -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours SET timezone = '00:00'; SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; -- 2.7.4 --------------65FB62A2E0811333017E53AC Content-Type: text/x-patch; name="0002-Jsonpath-engine-and-operators-v19.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Jsonpath-engine-and-operators-v19.patch"