From b64b857f50fb51da1588c54a56f8fc1c0d491058 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Mon, 16 Sep 2019 21:02:32 +0300 Subject: [PATCH] Support for SSSSS datetime format pattern SQL Standard 2016 defines SSSSS format pattern for seconds past midnight in jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause. In our datetime parsing engine we currently support it with SSSS name. This commit adds SSSSS as an alias for SSSS. Alias is added in favor of upcoming jsonpath .datetime() method. But it's also supported in to_date()/ to_timestamp() as positive side effect. Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com Author: Nikita Glukhov, Alexander Korotkov Reviewed-by: Anastasia Lubennikova, Peter Eisentraut --- doc/src/sgml/func.sgml | 2 +- src/backend/utils/adt/formatting.c | 12 ++++++++---- src/test/regress/expected/horology.out | 20 ++++++++++++++++++++ src/test/regress/sql/horology.sql | 4 ++++ 4 files changed, 33 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9a61f27674..2b4fe0cb59 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6175,7 +6175,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); microsecond (000000-999999) - SSSS + SSSS, SSSSS seconds past midnight (0-86399) diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index c6f992bfc1..053affa5ce 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -626,6 +626,7 @@ typedef enum DCH_PM, DCH_Q, DCH_RM, + DCH_SSSSS, DCH_SSSS, DCH_SS, DCH_TZH, @@ -678,6 +679,7 @@ typedef enum DCH_pm, DCH_q, DCH_rm, + DCH_sssss, DCH_ssss, DCH_ss, DCH_tz, @@ -787,7 +789,8 @@ static const KeyWord DCH_keywords[] = { {"PM", 2, DCH_PM, false, FROM_CHAR_DATE_NONE}, {"Q", 1, DCH_Q, true, FROM_CHAR_DATE_NONE}, /* Q */ {"RM", 2, DCH_RM, false, FROM_CHAR_DATE_GREGORIAN}, /* R */ - {"SSSS", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* S */ + {"SSSSS", 5, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* S */ + {"SSSS", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, {"SS", 2, DCH_SS, true, FROM_CHAR_DATE_NONE}, {"TZH", 3, DCH_TZH, false, FROM_CHAR_DATE_NONE}, /* T */ {"TZM", 3, DCH_TZM, true, FROM_CHAR_DATE_NONE}, @@ -839,7 +842,8 @@ static const KeyWord DCH_keywords[] = { {"pm", 2, DCH_pm, false, FROM_CHAR_DATE_NONE}, {"q", 1, DCH_Q, true, FROM_CHAR_DATE_NONE}, /* q */ {"rm", 2, DCH_rm, false, FROM_CHAR_DATE_GREGORIAN}, /* r */ - {"ssss", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* s */ + {"sssss", 5, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* s */ + {"ssss", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, {"ss", 2, DCH_SS, true, FROM_CHAR_DATE_NONE}, {"tz", 2, DCH_tz, false, FROM_CHAR_DATE_NONE}, /* t */ {"us", 2, DCH_US, true, FROM_CHAR_DATE_NONE}, /* u */ @@ -920,10 +924,10 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = { -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_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, + DCH_P_M, DCH_Q, DCH_RM, DCH_SSSSS, 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_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, -1, DCH_p_m, DCH_q, DCH_rm, DCH_sssss, DCH_tz, DCH_us, -1, DCH_ww, -1, DCH_y_yyy, -1, -1, -1, -1 /*---- chars over 126 are skipped ----*/ diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index 74ecb7c10e..6b53876e06 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -3077,6 +3077,14 @@ SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSS'); -- ok SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS'); ERROR: date/time field value out of range: "2015-02-11 86400" +SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSSS'); -- ok + to_timestamp +------------------------------ + Wed Feb 11 23:53:20 2015 PST +(1 row) + +SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS'); +ERROR: date/time field value out of range: "2015-02-11 86400" SELECT to_date('2016-13-10', 'YYYY-MM-DD'); ERROR: date/time field value out of range: "2016-13-10" SELECT to_date('2016-02-30', 'YYYY-MM-DD'); @@ -3140,4 +3148,16 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); 2012-12-12 12:00:00 -01:30 (1 row) +SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS'); + to_char +------------------ + 2012-12-12 43200 +(1 row) + +SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS'); + to_char +------------------ + 2012-12-12 43200 +(1 row) + RESET TIME ZONE; diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 3c8580397a..f7a9da1e95 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -480,6 +480,8 @@ SELECT to_timestamp('2016-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok SELECT to_timestamp('2015-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSS'); -- ok SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS'); +SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSSS'); -- ok +SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS'); SELECT to_date('2016-13-10', 'YYYY-MM-DD'); SELECT to_date('2016-02-30', 'YYYY-MM-DD'); SELECT to_date('2016-02-29', 'YYYY-MM-DD'); -- ok @@ -503,5 +505,7 @@ SELECT '2012-12-12 12:00'::timestamptz; SELECT '2012-12-12 12:00 America/New_York'::timestamptz; SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); +SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS'); +SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS'); RESET TIME ZONE; -- 2.40.0