Subject: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter

From: "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email-masked]>
Date: Wed, 22 Oct 2025 07:21:52 +0000

Please read https://stackoverflow.com/help/minimal-reproducible-example 

**Describe the issue**
We discovered a wrong result returned by the equality operator when comparing a date and a timestamp when the date operand is explicitly casted, but not the timestamp: 

`SELECT cast(? as date) = ? as eq1` 

returns `true`
- with first parameter being `java.sql.Date.valueOf(LocalDate.of(2013, 03, 21))`
- and second  parameter is ` java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14))`

we obtain the same result with the `::` syntax: `SELECT ?::date = ? as eq1` 

**Driver Version?** 
42.7.8

**Java Version?**
21

**OS Version?**
Debian 6.12.32-1 (2025-06-07) x86_64 GNU/Linux

**PostgreSQL Version?**
17.5-1.pgdg120+1

**To Reproduce**
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

public class Repro {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        String user = "postgres";
        String password = "mysecretpassword";
        Connection conn = DriverManager.getConnection(url, user, password);

        var date = java.sql.Date.valueOf(LocalDate.of(2013, 03, 21));
        var timestamp = java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14));

        var preparedStatement = conn.prepareStatement(
                "SELECT ? = ? as eq1," +
                        " ? = cast(? as timestamp) as eq2," +
                        " cast(? as date) = ? as eq3," +
                        " cast(? as date) = cast(? as timestamp) as eq4," +
                        " ? = ? as eq5," +
                        " ? = cast(? as date) as eq6," +
                        " cast(? as timestamp) = ? as eq7," +
                        " cast(? as timestamp) = cast(? as date) as eq8");

        preparedStatement.setDate(1, date);
        preparedStatement.setTimestamp(2, timestamp);
        preparedStatement.setDate(3, date);
        preparedStatement.setTimestamp(4, timestamp);
        preparedStatement.setDate(5, date);
        preparedStatement.setTimestamp(6, timestamp);
        preparedStatement.setDate(7, date);
        preparedStatement.setTimestamp(8, timestamp);

        preparedStatement.setTimestamp(9, timestamp);
        preparedStatement.setDate(10, date);
        preparedStatement.setTimestamp(11, timestamp);
        preparedStatement.setDate(12, date);
        preparedStatement.setTimestamp(13, timestamp);
        preparedStatement.setDate(14, date);
        preparedStatement.setTimestamp(15, timestamp);
        preparedStatement.setDate(16, date);

        ResultSet rs = preparedStatement.executeQuery();
        rs.next();
        for (var col : List.of("eq1", "eq2", "eq3", "eq4", "eq5", "eq6", "eq7", "eq8")) {
            System.out.println(col + ": " + rs.getBoolean(col));
        }
    }
}
```

**Expected behaviour**
This code outputs:
```
eq1: false
eq2: false
eq3: true
eq4: false
eq5: false
eq6: true
eq7: false
eq8: false
```

we would expect all expressions evaluated to false
```
eq1: false
eq2: false
eq3: false
eq4: false
eq5: false
eq6: false
eq7: false
eq8: false
```

** Note**

I directly made a simple test on dbfiddle to test if the problem does not comes from posgresql itself and I was not able to reproduce this issue:

<img width="1054" height="494" alt="Image" src="https://github.com/user-attachments/assets/edb085b7-c41f-4a91-8a63-0d415805c96e" />

 So I suspect that his is the jdbc driver that does not communicate correctly the value/type of a timestamp parameter.

**Logs**
No special logs output.

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Wed, 22 Oct 2025 08:24:24 +0000

Hi,

It feels like the jdbc driver is not properly propagating the type of the parameters, for example if I do:
```java
        var date = java.sql.Date.valueOf(LocalDate.of(2013, 03, 21));
        var timestamp = java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14));
        var preparedStatement = conn.prepareStatement("SELECT pg_typeof(?), pg_typeof(?)");
        preparedStatement.setDate(1, date);
        preparedStatement.setTimestamp(2, timestamp);
```

I get the error: ERROR: could not determine data type of parameter $1

This means that the problem is not only with timestamp, but also with date.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 22 Oct 2025 08:49:19 +0000

What do you mean by "properly"?

Frankly, I would suggest to use `java.time` types instead of `java.sql` types.
Is there a reason you have to use `java.sql` types without casts?

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Wed, 22 Oct 2025 08:57:33 +0000

@vlsi 

> What do you mean by "properly"?

I guess I mean that when you look at the implementation of `PgPreparedStatement#setDate(int, java.sql.Date, java.util.Calendar)` or `PgPreparedStatement#setTimestamp(int, java.sql.Timestamp, java.util.Calendar)`, you can see it's using `Oid.UNSPECIFIED` most of the time to propagate the type to the server.

I just discovered this, I didn't know it when I wrote my first comment but I was intuiting this kind of thing.

> Frankly, I would suggest to use java.time types instead of java.sql types.

So you mean one should be doing `setObject(LocalDate.of(2013, 03, 21))` instead of `setDate(java.sql.Date.valueOf(LocalDate.of(2013, 03, 21)))` right?

It's indeed a good workaround right now, it's unfortunately not followed by libs like Hibernate that will directly use `setTimestamp()` or `setDate()` but I guess it's also workaroundable.

>  Is there a reason you have to use java.sql types without casts?

Is there a reason that we should be using casts? I mean: the only workaround currently is to do use casts, so that's what is going to be done, but we thought there was maybe something wrong going on a the driver level and decided to start a discussion here about it.

---

From: "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email-masked]>
Date: Wed, 22 Oct 2025 09:16:23 +0000

I can confirm that using directly `java.time` types and `setObject()` works as expected.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 22 Oct 2025 09:53:03 +0000

`Oid.DATE`, `Oid.TIMESTAMP`, and `Oid.TIMESTAMPTZ` differ.
At the same time, it is hard to tell if `.setTimestamp` means `TIMESTAMP` or `TIMESTAMPTZ`.

I'm not sure if it is the only reason for going with `UNSPECIFIED` when sending the dates to the backend, however, it looks like the primary way of moving forward should be `java.time` types.

Is there anything stopping you from using `java.time` with Hibernate?

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:11:30 +0000

> Oid.DATE, Oid.TIMESTAMP, and Oid.TIMESTAMPTZ differ.
> At the same time, it is hard to tell if .setTimestamp means TIMESTAMP or TIMESTAMPTZ.

So basically, this is a known behaviour of the driver and there is no way to improve it? Thanks for the explanation, it's unfortunate because it's very error-prone, but I guess we have no real choice here.

> Is there anything stopping you from using java.time with Hibernate?

Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to `java.sql.Timestamp` before calling `setTimestamp` on the statement :)

But the easy workaround is to use a cast. I may check if the currently supported version of Hibernate still have this problem (we are stuck with v5) and if they do I will report a bug there.

Feel free to close this ticket if you think there is nothing more that can be done on the driver side :)

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:24:00 +0000

> So basically, this is a known behaviour of the driver and there is no way to improve it?

I'm afraid it would be extremely hard to improve the behavior due to backward compatibility reasons.

> it's very error-prone

This is fair. I've filed an enhancement request for error-prone: https://github.com/google/error-prone/issues/5291

>Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to java.sql.Timestamp before calling setTimestamp on the statement :)

Is it really the case?
Frankly, I think pgjdbc supports `java.time` for quite some time already, and ORMs should migrate to that API as it is a much cleaner mapping between Java and database types.

If Hibernate still uses `java.sql`, I would suggest filing a ticket to make them migrate to `java.time` instead.
They should raise warnings if the application still uses `java.sql` fields.

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:29:49 +0000

> Is it really the case?

At least on Hibernate 5, see the `doBind` methods in https://github.com/hibernate/hibernate-orm/blob/5.6/hibernate-core/src/main/java/org/hibernate/type/descriptor/sql/TimestampTypeDescriptor.java

> Frankly, I think pgjdbc supports java.time for quite some time already, and ORMs should migrate to that API as it is a much cleaner mapping between Java and database types.

Yep, I agree, I will followup with Hibernate on this (for v6+ as v5 is no longer supported)

> They should raise warnings if the application still uses java.sql fields.

Just to be clear, this is even worst: even if the application is not, their code is (at least on v5, again ;)

Great idea with the error-prone ticket!!

---

From: "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:30:12 +0000

@vlsi  Just one thing. I understand it's not possible to tell exactly what parameter type it is when we use `setTimestamp()` but I don't get the reason why we couldn't determine the type of the parameter given by `setDate()`. As stated in the documentation `java.sql.Date` type is supposed to be used with zero valued time fields hence that should be exactly a sql `date` without any ambiguity.

```

public class Repro {

        var preparedStatement = conn.prepareStatement(
                "SELECT pg_typeof(?) as theType");

        ResultSet rs = preparedStatement.executeQuery();
        rs.next();
        System.out.println("theType" + ": " + rs.getString("theType"));
    }
}
```

this code throws the following exception and that's seams like not normal to me:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2736)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:525)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:196)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:139)
	at Repro.main(Repro.java:22)
```

I understand this is related to an old api, maybe that's not worth fixing that. I'm just thinking there is a maybe a very quick fix to provide the correct type.

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:33:05 +0000

About Hibernate 6, a quick check shows that it uses `setObject` directly with the `java.time` object, so it should be fine there!

---

From: "tbroyer (@tbroyer)" <[email-masked]>
Date: Wed, 22 Oct 2025 10:47:58 +0000

And AFAICT, `java.sql.Timestamp` is by definition a `timestamp`, not a `timestamptz`

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 22 Oct 2025 12:32:21 +0000

> And AFAICT, `java.sql.Timestamp` is by definition a `timestamp`, not a `timestamptz`

I gather you are suggesting that we should always treat it as a timestamp then? If so how would one set a timestamptz in older versions of the JDK ?

---

From: "tbroyer (@tbroyer)" <[email-masked]>
Date: Wed, 22 Oct 2025 13:01:56 +0000

> I gather you are suggesting that we should always treat it as a timestamp then?

Yes

> If so how would one set a timestamptz in older versions of the JDK ?

You can't.

I don't make the rules, the JCP does.

And [JDBC 4.3 (pdf)](https://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf) only lists TIMESTAMP_WITH_TIMEZONE associated with java.time.OffsetDateTime when java.sql.Timestamp is only ever associated with TIMESTAMP, and [JDBC 2.1](https://web.archive.org/web/20001018172021/http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/appendixA.html) which added "support for time zones" back in the day only added the `setTimestamp()` overloads taking an additional `Calendar` argument, but note that [the javadoc](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html#setTimestamp(int,java.sql.Timestamp,java.util.Calendar)) says it uses the `Calendar` object to determine the time zone to use to compute the value instead of the local time zone, but still constructs a `TIMESTAMP` SQL value!

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 23 Oct 2025 04:22:03 +0000

> And [JDBC 4.3 (pdf)](https://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf) only lists TIMESTAMP_WITH_TIMEZONE associated with java.time.OffsetDateTime when java.sql.…

Well, Postgres has always had 2 timestamp types and the driver is for Postgres so we support both. I'll also note that timestamp is of questionable value https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don't_use_timestamp_(without_time_zone)

Either way we will continue to support both as we attempt to provide a client that can read and write all postgres types

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Thu, 04 Dec 2025 09:32:52 +0000

> Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to `java.sql.Timestamp` before calling `setTimestamp` on the statement :)

Are you aware that recent Hibernate versions have a setting to control this behavior? See [JAVA_TIME_USE_DIRECT_JDBC](https://docs.hibernate.org/orm/7.2/javadocs/org/hibernate/cfg/MappingSettings.html#JAVA_TIME_USE_DIRECT_JDBC)

---

From: "victornoel (@victornoel)" <[email-masked]>
Date: Thu, 04 Dec 2025 10:10:59 +0000

@m-van-tilburg yep indeed, I'm still stuck with Hibernate 5 but this would the proper way to deal with this problem with Hibernate 6+, thanks for mentioning it :)