Message-ID: <gh-pgjdbc-pgjdbc-4039@github.com>
From: "labkey-martyp (@labkey-martyp)" <noreply+labkey-martyp@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Mon, 27 Apr 2026 13:10:01 +0000
Subject: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 10213376
X-GitHub-Author-Login: labkey-martyp
X-GitHub-Issue: 4039
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: closed
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/4039
Content-Type: text/plain; charset=utf-8

**Describe the issue**
This [issue](https://www.postgresql.org/message-id/CAJnjrPND0MiidV%2BzRgzmL4zb5oYv9TgCUwtYqD4yW3%2Bk4Cc%2BCg%40mail.gmail.com) 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
```