pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: labkey-martyp (@labkey-martyp) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
Date: Mon, 27 Apr 2026 13:10:01 +0000
Message-ID: <[email protected]> (raw)

**Describe the issue**
This [issue](https://www.postgresql.org/message-id/CAJnjrPND0MiidV%2BzRgzmL4zb5oYv9TgCUwtYqD4yW3%2Bk4Cc%2BCg%40ma...) was filed to postgres but needed to be filed here.

Timestampdiff swaps parameters when using parameter markers `?`.

**Driver Version** 
42.7.9

**Java Version**
25.0.1+8-LTS

**OS Version**
Windows 11

**PostgreSQL Version**
18.3

**To Reproduce**
Steps to reproduce the behaviour:

Below is a running code function (except for creating the JDBC Connection).    The version that uses string literals return 366 and the versions that use parameter markers return -366.

```java
static void testTimestampDiffParameters(Connection conn) throws SQLException
    {
        // WITHOUT PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST('01 Jan 2000 12:00' AS TIMESTAMP), CAST('01Jan 2001 12:00' AS TIMESTAMP))"))
        {
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/o parameters: " + rs.getInt(1));
            }
        }

        // WITH PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(? AS TIMESTAMP), CAST(? AS TIMESTAMP))"))
        {
            stmt.setString(1,"01 Jan 2000 12:00");
            stmt.setString(2,"01 Jan 2001 12:00");
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/ parameters: " + rs.getInt(1));
            }
        }

        // WITH PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP), CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP))"))
        {
            stmt.setString(1,"01 Jan 2000 12:00");
            stmt.setString(2,"01 Jan 2001 12:00");
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/ parameters varchar: " +
                        rs.getInt(1));
            }
        }
    }
```

Since the parameters to DateDiff are swapped from {fn timestampdiff()}, the
obvious guess would be that the driver is swapping the arguments, but not
remapping the JDBC parameter indexes to the new swapped location.

**Expected behaviour**
Expected: The same result for all three executeQuery() calls.
Actual: Using string literals results in 366. Using parameter markers gets -366.

**Logs**

```text
Output:
    w/o parameters: 366
    w/ parameters: -366
    w/ parameters varchar: -366
```

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox