java - jooq TIMESTAMP(6) 支持

Question

我正在尝试使用 Java Jooq 库在 MariaDB 表中插入一个毫秒精度的时间戳，其中我有一个 TIMESTAMP(6) 类型的列。代码行，作为一个例子是：

eventRecord.setEventtimestamp(LocalDateTime.ofEpochSecond(Instant.parse("2021-05-18T16:47:31.862750Z").getEpochSecond() , Instant.parse("2021-05-18T16:47:31.862750Z").getNano(), ZoneOffset.UTC));

不幸的是，MariaDB 中的结果被截断并且没有插入毫秒：

2021-05-18 16:47:31.000000

表结构为：

MariaDB [plc_data]> DESCRIBE events;
+---------------------+--------------+------+-----+------------------+-------+
| Field               | Type         | Null | Key | Default          | Extra |
+---------------------+--------------+------+-----+------------------+-------+
| event_id            | bigint(20)   | NO   | PRI | NULL             |       |
| eventTimestamp      | timestamp(6) | YES  |     | NULL             |       |
| insertTimestamp     | timestamp(6) | YES  |     | utc_timestamp(6) |       |
| machineSerialNumber | int(11)      | NO   |     | NULL             |       |
| data_block          | longtext     | NO   |     | NULL             |       |
+---------------------+--------------+------+-----+------------------+-------+

以及为两个时间戳字段生成的代码：

    /**
     * The column <code>plc_data.events.eventTimestamp</code>. GMT
     */
    public final TableField<EventsRecord, Instant> EVENTTIMESTAMP = createField(DSL.name("eventTimestamp"), org.jooq.impl.SQLDataType.LOCALDATETIME.defaultValue(org.jooq.impl.DSL.field("NULL", org.jooq.impl.SQLDataType.LOCALDATETIME)), this, "GMT", new TimestampConverter());

    /**
     * The column <code>plc_data.events.insertTimestamp</code>. GMT
     */
    public final TableField<EventsRecord, Instant> INSERTTIMESTAMP = createField(DSL.name("insertTimestamp"), org.jooq.impl.SQLDataType.LOCALDATETIME.defaultValue(org.jooq.impl.DSL.field("utc_timestamp(6)", org.jooq.impl.SQLDataType.LOCALDATETIME)), this, "GMT", new TimestampConverter());

TimestampConverter您可以在生成的代码中看到的类是我编写的自定义转换器：

 * Jooq converter to use Instant instead of SQL TIMESTAMP which is mapped
 * into LocalDateTime
 */
public class TimestampConverter implements Converter<LocalDateTime, Instant> {

    private static final long serialVersionUID = -2866811348870878385L;

    /**
     * Convert from {@code LocalDateTime} to {@code Instant}
     */
    @Override
    public Instant from(LocalDateTime databaseObject) {
        return databaseObject.atZone(ZoneOffset.UTC).toInstant();
    }

    /**
     * Convert from {@code Instant} to {@code Timestamp}
     */
    @Override
    public LocalDateTime to(Instant userObject) {
        return userObject.atZone(ZoneOffset.UTC).toLocalDateTime();
    }

    /**
     * Return the from Type Class (Database Type Class)
     */
    @Override
    public Class<LocalDateTime> fromType() {
        return LocalDateTime.class;
    }

    /**
     * Return the to Type Class (User type Class)
     */
    @Override
    public Class<Instant> toType() {
        return Instant.class;
    }
}

指示 Jooq 插入我的日期的毫秒部分的方法是什么？

问候，

S。

标签： javamariadbjooqjava-time