agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Nikita Glukhov <[email protected]>
Subject: [PATCH 1/4] Preliminary datetime infrastructure
Date: Tue, 6 Nov 2018 16:33:13 +0300
---
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"
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 1/4] Preliminary datetime infrastructure
In-Reply-To: <no-message-id-238023@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox