1

I'm trying to use JOOQ to insert a row to MySQL 8 with Instant <--> DATETIME mapping, but I'm getting org.jooq.exception.DataAccessException with root cause com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2020-05-02 02:45:38.134463+00:00' for column 'created' at row 1

For some reason JOOQ adds +00:00 to DATETIME column and that format is not supported by MySQL.

Any ideas if JOOQ can manage that out-of-the-box?

CREATE TABLE `user` (
    `id`       BIGINT       NOT NULL AUTO_INCREMENT,
    `version`  BIGINT       NOT NULL DEFAULT 0,
    `name`     VARCHAR(255) NOT NULL,
    `created`  DATETIME     NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ind_name`(`name`)
);

@Getter
@Setter
public class User {
    @NonNull
    @Id
    private Long id;
    @NonNull
    @Version
    private Long version;
    @NonNull
    @Column(name = "name")
    private String name;
    @NonNull
    @Column(name = "created")
    private Instant created;
}

dslContext.insertInto( DSL.table( "user" ), field( "name", String.class ),
                //or field( "created", Instant.class), the result is the same
                field( "created", SQLDataType.INSTANT ) ) 
                .values( name, Instant.now() )
                .returningResult( DSL.asterisk() )
                .fetchOne()
                .into( User.class );
Mikhail Kopylov
  • 2,008
  • 5
  • 27
  • 58
  • I can think of one possible easy fix for half of the problem; `created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` (from the [MySQL Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html)). – Elliott Frisch May 02 '20 at 04:26
  • That does not solve the problem unfortunately. The question was about mapping from Instant to DATETIME and vice-versa. – Mikhail Kopylov May 02 '20 at 04:30
  • I thought it might solve half of the problem, namely the need to write that field on insert. Be patient, someone will hopefully have a better answer. – Elliott Frisch May 02 '20 at 04:38
  • Why are you using Instant and not LocalDateTime? Do you need the timezone part of Instant? – Simon Martinelli May 02 '20 at 08:10
  • 1
    @SimonMartinelli Neither Instant nor LocalDateTime have zone offset, but their sense is still different. While LocalDateTime assumes local zone which might be different for each user, Instant is effectively a number of millis from 1970. And I need the latter one. Hope that makes sense. – Mikhail Kopylov May 02 '20 at 08:36
  • Oh you are right. Read the api doc: https://www.jooq.org/javadoc/3.12.x/org.jooq/org/jooq/impl/SQLDataType.html#INSTANT This SQL Type is not the same as Instant. So try using LOCALDATETIME – Simon Martinelli May 02 '20 at 08:43
  • @SimonMartinelli sorry, probably didn't get the idea, but if I use `field( "created", SQLDataType.LOCALDATETIME )` then JOOQ forces me to pass `java.time.LocalDateTime`, while I use `java.time.Instant` – Mikhail Kopylov May 02 '20 at 12:18
  • Yeah I see. Anyway the JavaDoc says: "Neither JDBC, nor most SQL databases support the INSTANT data type" So please have a look at this question: https://stackoverflow.com/questions/47198806/how-to-store-a-java-instant-in-a-mysql-database – Simon Martinelli May 02 '20 at 14:11
  • 1
    @SimonMartinelli Yeah, thanks, and I'm going to use DATETIME column type in MySQL. Value in this DATETIME column is intended to store value in UTC. It seems if I were using LocalDateTime java type I had no problems with that. But I would like to use Instant java type for that. Can JOOQ manage such use cases? – Mikhail Kopylov May 02 '20 at 14:53
  • But why do you want to use a type that has higher precision than what you can store in the DB? – Simon Martinelli May 02 '20 at 15:00
  • And another question: Why don't you use the jOOQ Generator to generate the User class? – Simon Martinelli May 02 '20 at 15:00
  • 1
    @SimonMartinelli Effectively, Instant doesn't have higher precision than LocalDateTime: both have `nano` part and none has zone. So it's more a question of sense - it seems more correct for me to use Instant in java code to operate with a value that represents some point in time. As for jOOQ Generator - indeed, I'm just starting using jOOQ and trying to learn it gradually, so haven't tried Generator yet. Will it generate LocalDateTime? It seems, I'll face the same issue then. – Mikhail Kopylov May 02 '20 at 15:21

0 Answers0