TIMESTAMPDIFF(TSI_SQL_SECOND,d1,d2) has limit of 10^9-1

[Mick Francis]

If two timestamps differ by 10^9 or more seconds, TIMESTAMPDIFF(TSI_SQL_SECOND,d1,d2) fails:

SELECT timestampdiff(SQL_TSI_SECOND,#1961-08-04 22:30:59#,#1993-04-13 00:17:38#) from dual;

·-----------·
|        C1 |
·-----------·
| 999999999 |
·-----------·

SELECT timestampdiff(SQL_TSI_SECOND,#1961-08-04 22:30:59#,#1993-04-13 00:17:39#) from dual;
UCAExc:::4.0.4 data exception: interval field overflow

I guess this arbitrary limit is in the database engine? I would have expected 2^31-1.

Cheers,

Mick.

[Gord Thompson]

This appears to be a bug in HSQLDB 2.3.1 that has since been fixed

// vanilla HSQLDB code
try (Statement st = conn.createStatement()) {
    ResultSet rs = st.executeQuery("select TIMESTAMPDIFF(SQL_TSI_SECOND,CAST('1951-08-04 22:30:59' AS DATETIME),CAST('1993-04-13 00:17:38' AS DATETIME)) from (VALUES(0))");
    rs.next();
    System.out.println(rs.getInt(1));
}

... throws a "java.sql.SQLDataException: data exception: interval field overflow" under 2.3.1 but returns 1315619199 under 2.4.1.

UCanAccess_5 will use HSQLDB 2.4.x (or newer) so this problem should go away.

[Mick Francis]

Great - thanks Gord!