Query ran through JDBC returns zero results while PSQL returns correct amount-CodePudding

I'm querying a View in PostgreSQL and am getting two different results between JDBC and the PSQL console. The PSQL console does contain the correct results.

Here is a contrived example of what is happening.

Imagine the view as follows:

Field	Type
id	integer
org_id	integer
custom_attributes	jsonb
time_period	tstzrange

and the query being executed is as follows:

SELECT
    t0.id
FROM
    profile_view t0
WHERE
    t0.time_period @> '2022-04-11 14:21:27.862453'::timestamptz
    AND t0.org_id = 270
    AND t0.custom_attributes IS NOT NULL;

Imagine the database has 119 records with custom attributes, and 567 records without custom attributes.

When executing this in PSQL, I will get 119 records, but when this is executed through JDBC I get no records. Logging was enabled through PostgreSQL and the queries executed were identical.

Two interesting things of note:

When I change the query to have AND t0.custom_attributes IS NULL I get back the expected 567 records in JDBC and PSQL.
When I remove the t0.time_period @> '2022-04-11 14:21:27.862453'::timestamptz I get expected 119 records in JDBC and PSQL.

Any ideas as to what's going on here?

CodePudding user response：

I guess that the problem is in the cast of the string literal to timestamp with time zone. The result of such a type cast will depend on the current setting of the parameter TimeZone.

I see two remedies:

make sure that TimeZone is always set to the same value, by explicitly setting it in your application
include a time zone offset in your string literal, e.g.
```
'2022-04-11 14:21:27.862453 00'
```