From 8ba6fdf905d0f5aef70ced4504c6ad297bfe08ea Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 25 Jan 2024 17:47:08 -0500 Subject: Support TZ and OF format codes in to_timestamp(). Formerly, these were only supported in to_char(), but there seems little reason for that restriction. We should at least have enough support to permit round-tripping the output of to_char(). In that spirit, TZ accepts either zone abbreviations or numeric (HH or HH:MM) offsets, which are the cases that to_char() can output. In an ideal world we'd make it take full zone names too, but that seems like it'd introduce an unreasonable amount of ambiguity, since the rules for POSIX-spec zone names are so lax. OF is a subset of this, accepting only HH or HH:MM. One small benefit of this improvement is that we can simplify jsonpath's executeDateTimeMethod function, which no longer needs to consider the HH and HH:MM cases separately. Moreover, letting it accept zone abbreviations means it will accept "Z" to mean UTC, which is emitted by JSON.stringify() for example. Patch by me, reviewed by Aleksander Alekseev and Daniel Gustafsson Discussion: https://postgr.es/m/1681086.1686673242@sss.pgh.pa.us --- src/backend/utils/adt/jsonpath_exec.c | 18 ++++++------------ 1 file changed, 6 insertions(+), 12 deletions(-) (limited to 'src/backend/utils/adt/jsonpath_exec.c') diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 02a700292bb..22f598cd359 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -2296,20 +2296,14 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, static const char *fmt_str[] = { "yyyy-mm-dd", /* date */ - "HH24:MI:SS.USTZH:TZM", /* timetz */ - "HH24:MI:SS.USTZH", - "HH24:MI:SSTZH:TZM", - "HH24:MI:SSTZH", + "HH24:MI:SS.USTZ", /* timetz */ + "HH24:MI:SSTZ", "HH24:MI:SS.US", /* time without tz */ "HH24:MI:SS", - "yyyy-mm-dd HH24:MI:SS.USTZH:TZM", /* timestamptz */ - "yyyy-mm-dd HH24:MI:SS.USTZH", - "yyyy-mm-dd HH24:MI:SSTZH:TZM", - "yyyy-mm-dd HH24:MI:SSTZH", - "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH:TZM", - "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH", - "yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM", - "yyyy-mm-dd\"T\"HH24:MI:SSTZH", + "yyyy-mm-dd HH24:MI:SS.USTZ", /* timestamptz */ + "yyyy-mm-dd HH24:MI:SSTZ", + "yyyy-mm-dd\"T\"HH24:MI:SS.USTZ", + "yyyy-mm-dd\"T\"HH24:MI:SSTZ", "yyyy-mm-dd HH24:MI:SS.US", /* timestamp without tz */ "yyyy-mm-dd HH24:MI:SS", "yyyy-mm-dd\"T\"HH24:MI:SS.US", -- cgit v1.2.3