Defect in H2 TRUNC function

Question

Starting from version 1.3.172, there is a TRUNC function which is supposed to mimic the Oracle's TRUNC(TIMESTAMP). There is a slight problem with the implementation. Query:

select TRUNC(TIMESTAMP '2000-02-04 11:13:14') from dual;

returns "2000-02-04 00:00:00" as expected, but:

select TRUNC(TIMESTAMP '2000-02-04 15:13:14') from dual;

returns "2000-02-04 12:00:00", which is wrong.

The reason for this is the incorrect implementation (starting from line 1031 of Function.java), we see:

case TRUNCATE: {
    if (v0.getType() == Value.TIMESTAMP) {
        java.sql.Timestamp d = v0.getTimestamp();
        Calendar c = Calendar.getInstance();
        c.setTime(d);
        c.set(Calendar.HOUR, 0);
        c.set(Calendar.MINUTE, 0);

The c.set(Calendar.HOUR, 0) should be replaced by c.set(Calendar.HOUR_OF_DAY, 0) --- otherwise it truncates the 12-hour clock (which mimics the oracle version only for times before noon).

Is there any hope of including this kind of fix in near future?

I'd push the fix myself, but I 'stackoverflow h2 tag' was the only 'support' link I could find, so that's where I'm reporting the problem.

In the meantime (while I wait for the fix to become accessible), is it possible to replace (say, by dropping the existing alias and replacing it with a different implementation)?

Answer 1

In the H2 web site, there are 3 links under "Support": StackOverflow, Google Group English and Japanese. For bug reports, it makes sense to use the Google Group.

The feature to truncate timestamps is relatively new. The bug you found was already found by somebody else and is fixed in the trunk. If you want, you can try it out by downloading and building H2 yourself.