TIMESTAMPDIFF(SQL_TSI_QUARTER,d1,d2) incorrect

[Mick Francis]

Hi,

The use of SQL_TSI_QUARTER in a TIMESTAMPDATEDIFF seems to just do a difference in months and divide by 3, rather than respecting the Quarter boundaries:

select TIMESTAMPDIFF(SQL_TSI_QUARTER,#2019-03-31#,#2019-04-01#) from dual;
·----·
| C1 |
·----·
|  0 |  Should be 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_QUARTER,CAST('2019-03-31 00:00:00' AS DATETIME),CAST('2019-04-01 00:00:00' AS DATETIME)) from (VALUES(0))");
    rs.next();
    System.out.println(rs.getInt(1));
}

... returns 0 under 2.3.1 but returns 1 under 2.4.1.

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

[Mick Francis]

That's great to hear - Thanks Gord. Any idea when UCanAccess_5 will be released?