jsonb_path_query_tz()

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-only value 2015-08-02 as a timestamp with time zone, so the result depends on the current TimeZone setting. Due to this dependency, these functions are marked as stable, which means these functions cannot be used in indexes. Their counterparts are immutable, and so can be used in indexes; but they will throw errors if asked to make such comparisons.

jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

SELECT jsonb_path_query_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())');

"2015-08-01 12:00:00-05"

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.