Re: jsonpath: Inconsistency of timestamp_tz() Output

On Jul 10, 2024, at 10:33, David E. Wheeler <david@justatheory.com> wrote:

> Yeah I don’t know either, but now at least it’s consistent. I’ve attached a patch to fix it.

Actually I think there’s a subtlety still missing here:

@@ -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-14T23:00:00-08:00"
 (1 row)

This test runs while the time zone is set to “PST8PDT”, but it’s got the PST offset when it should be PDT:

david=# set time zone 'PST8PDT';
SET
david=# select '2023-08-15'::timestamptz;
      timestamptz
------------------------
 2023-08-15 00:00:00-07

So it should be -7, not -8. Not sure where to tell it to pay proper attention to daylight savings time.

Best,

David