Jooq: Add a new INSTANT data type that models UTC timestamps

[lukaseder]

Unfortunately, the SQL TIMESTAMP WITH TIME ZONE data type isn't always implemented in the same way. Users may want to map it to a predicatble java.time.Instant type, which doesn't hold any time zone information and is thus always in UTC (just like in PostgreSQL, except that PostgreSQL "formats" timestamps in the default time zone of the client)

[coudy]

Not sure if already covered by the above but TIMESTAMP WITHOUT TIME ZONE would also benefit from being converted to/from java.time.Instant rather than the current java.time.LocalDateTime.

[lukaseder]

@coudy: What would be the correct Instant for TIMESTAMP '1970-01-01 00:00:00.0'? You might be inclined to say Instant.EPOCH, but that's only correct if you're running the database server in UTC.

If you think that's what you should be doing, you can always use a Converter to map all TIMESTAMP types in your database to Instant, but I think it would be unwise for jOOQ to default to this behaviour.

[coudy]

Running your DB in UTC is very common (default for RDS & Google Cloud SQL). I do understand if you don't want to make that default, though. My actual problem is that jOOQ isn't generic, i.e. defining a Converter doesn't make DSL.currentTimetamp() return an Instant, and I'm not sure how to best deal with that, redefining the SQL function in jOOQ isn't ideal.

[lukaseder]

Running your DB in UTC is very common (default for RDS & Google Cloud SQL)

Sure, but maybe it's just as common as running it in a local time zone. E.g. I know a Swiss bank (who only operates in Switzerland) that uses CET. Lucky them, not having to deal with other time zones.

defining a Converter doesn't make DSL.currentTimetamp() return an Instant and I'm not sure how to best deal with that, redefining the SQL function in jOOQ isn't ideal.

Yes, it's hard-wired to java.sql.Timestamp, like much of DSL. For instance, if you use an EnumConverter to map your SQL VARCHAR types, a lot of String-based functions from DSL won't work out of the box.

You can always roll your own mini API using plain SQL, though:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

[coudy]

Running your DB in UTC is very common (default for RDS & Google Cloud SQL)

Sure, but maybe it's just as common as running it in a local time zone. E.g. I know a Swiss bank (who only operates in Switzerland) that uses CET. Lucky them, not having to deal with other time zones.

I hope they're closed during DST switchover. 😛

You can always roll your own mini API using plain SQL, though:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

Thanks, I'll look into that. 👍

[lukaseder]

Of course. It's a bank. They're always closed on Sundays :)

[Yuhta]

I think the problem here is how do we save a Instant as UTC TIMESTAMP in DB. In JDBC this can be done with

PreparedStatement#setTimestamp(0, t, Calendar.getInstance(TimeZone.getTimeZone("UTC")))

but jOOQ does not provide any interface to that. The TIMESTAMP type accessor is hard-coded in a huge method body in DefaultBinding#set making it hard to override.

[lukaseder]

@Yuhta: If you want to get down to that JDBC level, the best way forward is to implement your own data type and custom data type binding:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

[Yuhta]

Hi @lukaseder, if I set up my custom binding, is it possible to override the default bindings so that I can do this and get myTimestamp saved in DB in UTC instead of local time zone?

create.execute("UPDATE A SET T = ?", myTimestamp);

[lukaseder]

Of course. Either, you need to register your binding in the code generator, or you can use it explicitly on existing data types, such as:

DataType<MyType> type = SQLDataType.TIMESTAMP.asConvertedDataType(new MyBinding());
Field<MyType> field = DSL.val(myTimestamp, type);
create.execute("update a set t = {0}", field);

[Yuhta]

Thanks @lukaseder, I am not using the code generator so have to use the second way. It's still a little awkward given that we need to carry DataType<Timestamp> everywhere but it's something. Is there a global registry we can put it so we don't need to pass it in every time we do a binding?

[lukaseder]

Is there a global registry we can put it so we don't need to pass it in every time we do a binding?

Yes. It's called the code generator ;-)

[lukaseder]

Duplicate of #7952 (implemented for 3.12)