Re: jsonpath: Inconsistency of timestamp_tz() Output - Mailing list pgsql-hackers

From David E. Wheeler
Subject Re: jsonpath: Inconsistency of timestamp_tz() Output
Date
Msg-id C1D5DC47-65AA-47A1-AFC8-77CE224D232C@justatheory.com
Whole thread Raw
In response to Re: jsonpath: Inconsistency of timestamp_tz() Output  ("David E. Wheeler" <david@justatheory.com>)
List pgsql-hackers
On Jul 2, 2024, at 10:53, David E. Wheeler <david@justatheory.com> wrote:

> ``` 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")');
> ```

FWIW I fixed this issue in my jsonpath port, which I released over the weekend.[1] You can see what I think should be
theproper output for these two examples in these Playground links, where the response will use your browser’s time
zone:[2], [3]. 

Best,

David


[1]: https://justatheory.com/2024/07/go-sqljson-path/
[2]: https://theory.github.io/sqljson/playground/?p=%2524.timestamp_tz%28%29&j=%25222023-08-15%2522&a=&o=49
[3]:
https://theory.github.io/sqljson/playground/?p=%2524.timestamp_tz%28%29&j=%25222023-08-15%252012%253A34%253A56%2522&a=&o=49




pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Windows: openssl & gssapi dislike each other
Next
From: Andres Freund
Date:
Subject: Should we work around msvc failing to compile tab-complete.c?