Query with NOW() in criteria behaves differently in psql vs. JDBC

Question

I am executing following query in psql via console and getting output :

select details
  from history_transactions
      ,history_operations
  where history_operations.transaction_id = history_transactions.id
    and type = 3
    and created_at >= NOW() - INTERVAL '5 minutes'

However when I call this code from my java program, it is not returning any output. The ResultSet is null. PFB my code:

Connection conn = getConnection();
java.sql.Statement stmt = null;
String sql ="select details from  history_transactions , history_operations where  history_operations.transaction_id=history_transactions.id and type =3  and  created_at >= NOW() - INTERVAL '5 minutes'";
try{
    stmt = conn.createStatement();
    rs = stmt.executeQuery(sql);
    while(rs.next()) {
        System.out.println("Inside resultset");
    }
}
catch(Exception e)
{
    e.printStackTrace();
}

Any idea where I am going wrong?

I am not getting any exception as well.

Note: If I change the interval from 5 minute to 6 hours or more it is working and giving me output. If I change the interval < 5 hours then the resultset is null. However If I login to psql server and execute the query as it is in the code. I am getting output.

I am using java version "1.8.0_151" and PostgreSQL JDBC 4.2 Driver, 42.2.1 - as per https://jdbc.postgresql.org/download.html - it is the suitable driver.

Even if the query does not return anything, the `ResultSet` won't be null — , Feb 02 '18 at 14:39
Infact rs.isBeforeFirst() && rs.isAfterLast() is returning false. I believe that means resultset has rows? — sweta, Feb 02 '18 at 15:35
@sweta - Are you connecting to a PostgreSQL server that is operating under a different time zone than your local time zone? If so, then `NOW()` may represent a different (timezone-adjusted) `yyyy-mm-dd ...` value and if your `created_at` column is not timezone-aware then the query could produce different results. — Gord Thompson, Feb 02 '18 at 16:10
@GordThompson - No, I am connecting the same Postgresql server. Its a remote connection. I have installed postgre db in AWS instance and connecting to that database instance via jdbc. — sweta, Feb 03 '18 at 16:05
Try `stmt.execute("set time zone 'UTC'");` before executing your SELECT query. — Gord Thompson, Feb 03 '18 at 18:14
Possible duplicate of https://stackoverflow.com/q/18447995/2144390 — Gord Thompson, Feb 03 '18 at 18:15
@GordThompson Thanks - stmt.execute("set time zone 'UTC'"); helped — sweta, Feb 05 '18 at 05:21

score 0 · Answer 1 · answered Feb 05 '18 at 22:44

The PostgreSQL NOW() function returns a timestamp with time zone value. In order to use that value against a column of type timestamp (without time zone) PostgreSQL needs to implicitly CAST that value to match the column type. However, testing shows that if the client and the server are set to different time zones the result of that CAST can be different when connected via psql vs. when connected via JDBC. For example:

Server time zone: UTC
Client time zone: America/Denver, a.k.a. "MST/MDT", currently UTC-7

When connected via psql,

SELECT CAST(CAST(NOW() AS timestamp) AS varchar)

returns the UTC value

2018-02-05 22:40:25.012933

but when connected via JDBC the same query returns the MST value

2018-02-05 15:40:57.288587

To return the UTC value under JDBC we can execute

set time zone 'UTC'

before running our SELECT query.

Query with NOW() in criteria behaves differently in psql vs. JDBC

1 Answers1