Обсуждение: pgsql: Support TZ and OF format codes in to_timestamp().

Поиск
Список
Период
Сортировка

pgsql: Support TZ and OF format codes in to_timestamp().

От
Tom Lane
Дата:
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

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/8ba6fdf905d0f5aef70ced4504c6ad297bfe08ea

Modified Files
--------------
doc/src/sgml/func.sgml                       |  10 +-
src/backend/utils/adt/datetime.c             |  76 ++++++++++++
src/backend/utils/adt/formatting.c           | 167 ++++++++++++++++++---------
src/backend/utils/adt/jsonpath_exec.c        |  18 +--
src/include/utils/datetime.h                 |   3 +
src/test/regress/expected/horology.out       |  82 ++++++++++++-
src/test/regress/expected/jsonb_jsonpath.out |  12 ++
src/test/regress/sql/horology.sql            |  22 +++-
src/test/regress/sql/jsonb_jsonpath.sql      |   2 +
9 files changed, 318 insertions(+), 74 deletions(-)