Subject: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping

From: "labkey-martyp (@labkey-martyp)" <[email-masked]>
Date: Mon, 27 Apr 2026 13:10:01 +0000

**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
```

---

From: "svendiedrichsen (@svendiedrichsen)" <[email-masked]>
Date: Mon, 27 Apr 2026 17:37:52 +0000

Is the TIMESTAMPDIFF function an official function from Postgres? I'm not able to find any docs for this.

---

From: "labkey-martyp (@labkey-martyp)" <[email-masked]>
Date: Mon, 27 Apr 2026 21:17:40 +0000

Thanks for looking into it. I looked a bit more in the code and docs and it looks like the driver has an escape function [implementation](https://github.com/pgjdbc/pgjdbc/blob/b04fc46af6c207bc7ce9e788fea8c43d18b73d0f/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java#L571) that will work when string literals are in the parameters, but for any escape scalar function if the output reorders the parameters then prepared statements are not supported (per the second paragraph [here](https://jdbc.postgresql.org/documentation/escapes/#escaped-scalar-functions)).