Re: jsonpath: Inconsistency of timestamp_tz() Output

On Jul 1, 2024, at 11:02, David E. Wheeler <david(at)justatheory(dot)com> wrote:

> Anyway, should the output of timestamptz JSONB values be made more consistent? I’m happy to make a patch to do so, but could use a hand figuring out where the behavior varies.

I think if the formatting was more consistent, the test output would be:

``` patch
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2914,7 +2914,7 @@ HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
jsonb_path_query_tz
-----------------------------
- "2023-08-15T07:00:00+00:00"
+ "2023-08-15T00:00:00-07:00"
(1 row)

select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
@@ -3003,7 +3003,7 @@ HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
jsonb_path_query_tz
-----------------------------
- "2023-08-15T12:34:56+00:00"
+ "2023-08-15T12:34:56+10:00"
(1 row)

select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
```

That second example is a bit different than I noticed up-thread, not just a formatting issue but the offset is never applied!. That test run under tz +10, and this is how it works with the non-JSONB data types:

david=# set time zone '+10';
SET
Time: 0.689 ms
david=# select '2023-08-15 12:34:56'::timestamptz;
timestamptz
------------------------
2023-08-15 12:34:56+10
(1 row)

Time: 0.491 ms
david=# select '2023-08-15 12:34:56'::timestamp::timestamptz;
timestamptz
------------------------
2023-08-15 12:34:56+10
(1 row)

Best,

David