Jooq: Add support for INTERVAL data types in code generator

[lukaseder]

Apparently, in Postgres it is possible to specify a variety of INTERVAL subtypes in DDL. It is also possible to use the generic INTERVAL type:

CREATE TABLE schedule(
    ...
    duration INTERVAL NOT NULL,
    ...
);

This is currently not supported by jOOQ. See the full discussion here:
https://groups.google.com/d/topic/jooq-user/KXkkNSfO1UY/discussion

Tasks

The following dialects will get improved INTERVAL support in the code generator:

• [x] CockroachDB (information_schema.columns.interval_type is there, but not implemented. But nothing needs to be done, we're already reading the correct type info from columns.crdb_sql_type)
• [x] H2 (information available in information_schema.columns.interval_type)
• [x] HSQLDB (information available in information_schema.columns.interval_type)
• [x] Oracle (seems to work out of the box)
• [x] PostgreSQL (information available in information_schema.columns.interval_type)
• [x] ~SQL Server~ (doesn't actually support the type)

Others will follow in future feature requests.

---

See also:

• https://github.com/jOOQ/jOOQ/issues/10389

[rlz]

Code generation does not work for Postgres 9.1, jOOQ 2.6.2 even for

CREATE TABLE schedule(
    ...
    duration INTERVAL DAY TO SECOND NOT NULL,
    ...
);

PostgreSQL supports the following restrictions for INTERVAL data type:

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• YEAR TO MONTH
• DAY TO HOUR
• DAY TO MINUTE
• DAY TO SECOND
• HOUR TO MINUTE
• HOUR TO SECOND
• MINUTE TO SECOND

http://www.postgresql.org/docs/9.1/static/datatype-datetime.html

[lukaseder]

Thanks for the documentation link. I hope I will have some time to see into this soon

[rlz]

If you do not find a time, I'm going to try to implement this next week.

I tried the newest version of jOOQ (3.0.0-RC1 from Maven Central) but it even generated invalid Java code. I'm going to look on it also next week, and if I will be able to reproduce this, then I fill a new bug report.

[lukaseder]

That would be great, if you could give it a shot! Feel free to reuse this issue here.

Note that, even if Postgres has a somewhat more liberal interpretation of what an INTERVAL can be, there are really only two types of intervals in the SQL standard:

• YEAR TO MONTH (and restricted precisions / formattings thereof, i.e. YEAR, MONTH)
• DAY TO SECOND (and restricted precisions / formattings thereof, i.e. DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, ...)

I have no plans of adding more types than these to jOOQ:

• org.jooq.types.YearToMonth
• org.jooq.types.DayToSecond

Now, your original bug report mentions a general INTERVAL data type (without choosing between any of the above two types). I wonder how this should be mapped in jOOQ...?

[lukaseder]

Related: #2267, which is fixed on Github master.

[paukr]

Hi,
any news on this? Right now it seems to me that I cannot use any of Postgres' interval types at the moment. Is that correct?

[lukaseder]

@Gishgork : That's correct. There's currently no news on this topic. Compared to other popular data types (e.g. hstore, json), intervals haven't been mentioned often in feature requests. We haven't had any plans to make this a priority, recently.

[jacobg]

+1 👍 :-)

[elnur]

Uh-oh. I thought JOOQ would give me access to intervals. I guess I'm staying with JPA.

[lukaseder]

@elnur: You'll be back ;)

[Neverminder]

+1

[valeyko]

+1

[Meyttt]

Still no news?

[lukaseder]

I'm looking at a variety of interval and data type related issues in jOOQ 3.14, looking into this now.

[lukaseder]

This is how we can identify interval types in at least PostgreSQL and H2:

create table t_intervals (
  id int not null,
  i interval, -- Not supported by H2
  i_year interval year,
  i_year_to_month interval year to month,
  i_month interval month,
  i_day interval day,
  i_day_to_hour interval day to hour,
  i_day_to_minute interval day to minute,
  i_day_to_second interval day to second,
  i_hour interval hour,
  i_hour_to_minute interval hour to minute,
  i_hour_to_second interval hour to second,
  i_minute interval minute,
  i_minute_to_second interval minute to second,
  i_second interval second,
  constraint pk_t_intervals primary key (id)
);

select column_name, data_type, datetime_precision, interval_type, interval_precision
from information_schema.columns
where table_name = 't_intervals';

Result (from PostgreSQL, H2 is similar)

column_name       |data_type|datetime_precision|interval_type   |interval_precision|
------------------|---------|------------------|----------------|------------------|
id                |integer  |                  |                |                  |
i                 |interval |                 6|                |                  |
i_year            |interval |                 6|YEAR            |                  |
i_year_to_month   |interval |                 6|YEAR TO MONTH   |                  |
i_month           |interval |                 6|MONTH           |                  |
i_day             |interval |                 6|DAY             |                  |
i_day_to_hour     |interval |                 6|DAY TO HOUR     |                  |
i_day_to_minute   |interval |                 6|DAY TO MINUTE   |                  |
i_day_to_second   |interval |                 6|DAY TO SECOND   |                  |
i_hour            |interval |                 6|HOUR            |                  |
i_hour_to_minute  |interval |                 6|HOUR TO MINUTE  |                  |
i_hour_to_second  |interval |                 6|HOUR TO SECOND  |                  |
i_minute          |interval |                 6|MINUTE          |                  |
i_minute_to_second|interval |                 6|MINUTE TO SECOND|                  |
i_second          |interval |                 6|SECOND          |                  |

We currently don't have a way to represent interval types other than INTERVAL (YearToSecond), INTERVAL YEAR TO MONTH (YearToMonth), and INTERVAL DAY TO SECOND (DayToSecond). This doesn't really matter, because the intervals are just truncated in case they're not representable. E.g.

insert into t_intervals (id, i_year, i_hour) values (1, '1-6', '1 12:30:00') returning i_year, i_hour;

Yields:

i_year|i_hour        |
------|--------------|
1 year|1 day 12:00:00|

[lukaseder]

Well, it seems that in jOOQ 3.14, the PostgreSQL implementation of INTERVAL works out of the box. The other ones (e.g. INTERVAL YEAR TO MONTH need to be verified.

[lukaseder]

When binding the H2 type, we might need to generate and parse actual interval literals, even as bind values. The parser has been enhanced to be able to do that: https://github.com/jOOQ/jOOQ/issues/10453

[lukaseder]

This has been implemented in jOOQ 3.14 for the following dialects:

• CockroachDB
• H2
• HSQLDB
• Oracle
• PostgreSQL

Other dialects might follow in the future, upon request.

[mschneid]

Hi Lukas
I ran into a problem when using the INTERVAL type with PostgreSQL 12 and JOOQ 3.14. The interval type has a resolution of 1 microsecond in PostgreSQL. As defined in SQLDataType, interval is first converted to YearToSecond, but this class only has a resolution of 1 millisecond. Thus the microseconds are lost. Did you intend this to happen or is this a bug? So far I have not found a workaround for this. This is probably related to #10452.

[lukaseder]

@mschneid: Thank you very much for your message. Can you please open a new issue with the usual details that help reproduce the issue? https://github.com/jOOQ/jOOQ/issues/new/choose