DSl.trunc removes timezone in H2

colinwerner commented 4 years ago

Expected behavior

DSL.trunc should respect the timezone in my timestamp with time zone field. ie trunc('2019-05-02T10:10:20-07:00', DatePart.HOUR) should return '2019-05-02T10:00:00-07:00'

Actual behavior

DSL.trunc rips out the timezone. ie trunc('2019-05-02T10:10:20-07:00', DatePart.HOUR) returns '2019-05-02T10:00Z'

Steps to reproduce the problem

I have a query that uses DSL.trunc(mt.RECORD_TIMESTAMP, DatePart.HOUR).as("date_hour") where RECORD_TIMESTAMP is timestamp with time zone.

In postgres this translates to: date_trunc('hour', PUBLIC.MY_TABLE.RECORD_TIMESTAMP) as DATE_HOUR which works fine

but in H2 it translates to: parsedatetime(formatdatetime(PUBLIC.SUBSCRIBER_USAGE_CDR.RECORD_TIMESTAMP, 'yyyy-MM-dd HH'), 'yyyy-MM-dd HH') DATE_HOUR,

ctx.visit(DSL.keyword("parsedatetime")).sql('(') .visit(DSL.keyword("formatdatetime")).sql('(').visit(date).sql(", ").visit(inline(format)).sql("), ").visit(inline(format)).sql(')'); break;

This might naive of me, but why not use truncate in h2 (http://www.h2database.com/html/functions.html#truncate)?

This is of particular concern when I merge records that are grouped into hour chunks. The timestamp is no longer valid (ie it's in the wrong timezone...)

Versions

jOOQ: 3.13.4
Java: open jdk 11.0.9
Database (include vendor): h2 1.4.200
OS: ubuntu

colinwerner commented 4 years ago

Ok, I see that trunc goes down to the day, which is not good. But it should still respect the timezone?

katzyn commented 4 years ago

TRUNC / TRUNCATE with datetime argument is deprecated for H2 2.0 and should not be used.

H2 1.4.197 and later versions have DATE_TRUNC function. It is not documented and is not officially supported by H2 1.4.197–1.4.200, but it will be supported by H2 2.0. Please note that its behavior is different between H2 1.4 and H2 2.0. H2 2.0 preserves the original data type of values (unless PostgreSQL compatibility mode is in use, in this mode DATE values are converted to TIMESTAMP WITH TIME ZONE values) and multiple other issues were fixed. H2 2.0 also supports unquoted datetime units and it is better to use them without quotes with this version. H2 1.4 requires datetime units in single quotes (like character strings).

PARSEDATETIME and FORMATDATETIME functions in H2 have very weird implementation and should be considered as not fully reliable, but, unfortunately, they don't have good alternatives for now. There are some plans to implement SQL:2016 functions in the future in the sane way.

Unsupported DATE_TRUNC in H2 1.4.200 preserves the time zone, but only when this function is used with real TIMESTAMP WITH TIME ZONE argument:

SELECT
    DATE_TRUNC('HOUR', '2019-05-02T10:10:20-07:00'),
    DATE_TRUNC('HOUR', TIMESTAMP WITH TIME ZONE '2019-05-02 10:10:20-07:00');
> 2019-05-03 01:00:00 2019-05-02 10:00:00-07

Supported DATE_TRUNC in H2 2.0 does not accept character string as second argument at all. But with TIMESTAMP WITH TIME ZONE argument the time zone is also preserved:

SELECT DATE_TRUNC(HOUR, TIMESTAMP WITH TIME ZONE '2019-05-02 10:10:20-07:00');
> 2019-05-02 10:00:00-07

The issue about DATE_TRUNC for H2 is here: #8828.

jOOQ / jOOQ