Add support for specifying timezone in `DSL#trunc`

Use case

Assuming PostgreSQL and the following table:

create table demo (
    id serial primary key,
    name text not null,
    time timestamptz not null
);

And sample data:

insert into demo (name, time)
values
('test1', '1970-01-01T00:00:00+00:00'),
('test2', '1970-01-01T23:30:00+00:00'),
('test3', '1970-01-02T00:00:00+00:00'),
('test4', '1970-01-02T00:30:00+00:00');

I need to find the records for which time matches the specific date. One would naturally attempt to solve this using the following query:

select * from demo where date_trunc('day', time) = '1970-01-01';

However, due to timezone that date_trunc will use in its calculation being dependent on the current connection (and by default on JDBC that's the JVM's timezone), the output of this query does not only depend on its inputs:

demo=> show timezone;
   TimeZone    
---------------
 Europe/Zagreb
(1 row)

demo=> select * from demo where date_trunc('day', time) = '1970-01-01';
 id | name  |          time          
----+-------+------------------------
  1 | test1 | 1970-01-01 01:00:00+01
(1 row)

demo=> set timezone='UTC';
SET
demo=> select * from demo where date_trunc('day', time) = '1970-01-01';
 id | name  |          time          
----+-------+------------------------
  1 | test1 | 1970-01-01 00:00:00+00
  2 | test2 | 1970-01-01 23:30:00+00
(2 rows)

Such query condition is written in jOOQ using something like:

DSL.trunc(DEMO.TIME, DatePart.DAY).eq(LocalDate.EPOCH.atStartOfDay().atOffset(ZoneOffset.UTC))

Note that because DEMO.TIME is of TableField<DemoRecord, OffsetDateTime> type (because it maps a timestamptz column), the Field#eq expects OffsetDateTime which leads to even bigger inconsistency:

demo=> show timezone;
   TimeZone    
---------------
 Europe/Zagreb
(1 row)

demo=> select * from demo where date_trunc('day', time) = '1970-01-01T00:00:00+00:00';
 id | name | time 
----+------+------
(0 rows)

demo=> set timezone='UTC';
SET
demo=> select * from demo where date_trunc('day', time) = '1970-01-01T00:00:00+00:00';
 id | name  |          time          
----+-------+------------------------
  1 | test1 | 1970-01-01 00:00:00+00
  2 | test2 | 1970-01-01 23:30:00+00
(2 rows)

The way I see it, this kind of query cannot be reliably written without specifying a timezone in date_trunc function, which is something that PostgreSQL supports. However, jOOQ's DSL#trunc doesn't support this at the moment.

Additionally, as I'm using PostgreSQL almost exclusively these days, I'm not sure whether other vendors support this in their date_trunc (or equivalent) functions.

Possible solution

Ideally, a DSL#trunc variant that allows specifying timezone.

Possible workarounds

ATM I'm using jOOQ templating based workaround along these lines:

private static Field<OffsetDateTime> dateTrunc(Field<OffsetDateTime> dateTime) {
    return field("date_trunc('day', {0}, 'UTC')", OffsetDateTime.class, dateTime);
}

jOOQ Version

jOOQ Open Source 3.17.4

Database product and version

PostgreSQL 14.5

Java Version

No response

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

No response

jOOQ / jOOQ