Cannot insert java.time.LocalDateTime with zero seconds

Question

Kotlin: 1.6.20

Ktorm: 3.4.1

MySQL Connector/J: 8.0.28

C3P0 Connection Pool: 0.9.5.5

I seem to be having a problem with "datetimes" (LocalDateTime) that have zero seconds. I know this would look like an issue with the JDBC driver but I tested the driver and it does not even accept LocalDateTime (only java.util.Date). This issue does not come up if the LDT does have seconds.

Am I doing something wrong?
Am I missing some configuration?
Have I got the types wrong?
Does anyone know of a workaround?

A minimum test that I am using to isolate the issue

const val tableName = "test"
const val fieldName = "ldt"

interface TestRecord : Entity<TestRecord> {

    companion object : Entity.Factory<TestRecord>()

    val ldt: LocalDateTime
}

object TestTable : Table<TestRecord>(tableName) {

    val ldt = datetime(fieldName).bindTo { it.ldt }
}

class BlahTest {

    @Test
    fun a() {

        val dataSource = DbConfig.from("test.properties").provideDataSource()
        dataSource.connection.use {
            it.prepareStatement("DROP TABLE IF EXISTS $tableName").execute()
            it.prepareStatement("CREATE TABLE IF NOT EXISTS $tableName ($fieldName TIMESTAMP)").execute()
        }
        val db = Database.connect(dataSource = dataSource, dialect = MySqlDialect())
        val tb = TestTable
        val ldt = LocalDateTime.ofEpochSecond(0, 0, ZoneOffset.UTC)
            .truncatedTo(ChronoUnit.SECONDS)
            //.plusSeconds(1) // with anything but 0 it will work
        db.insertOrUpdate(TestTable) {
            set(tb.ldt, ldt)
        }
    }
}

Produces an error

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'ldt' at row 1

Ktorm issue: https://github.com/kotlin-orm/ktorm/issues/391

score 2 · Accepted Answer · answered May 03 '22 at 15:36

The exception is formatting the timestamp value 0 as '1970-01-01 00:00:00', but MySQL is not.

https://dev.mysql.com/doc/refman/8.0/en/datetime.html says:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

For example, I'm in PDT, so when I try to insert a timestamp value 0, it adjusts it by 8 hours to '1969-12-31 16:00:00', which is not a legal value in the range for the TIMESTAMP type.

mysql> create table tablename (ldt timestamp);

mysql> insert into tablename values (from_unixtime(0));
ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 16:00:00' for column 'ldt' at row 1

So you could make sure to account for timezone when you generate your "zero" value to insert into a TIMESTAMP.

Or you could use DATETIME.

mysql> alter table tablename modify ldt datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tablename values (from_unixtime(0));
Query OK, 1 row affected (0.00 sec)

I recommend using DATETIME anyway, because it supports a greater range of dates than TIMESTAMP. This will become important if you need to store dates past 2038-01-19.

The solution I am looking for does not involve writing bare mysql statements. I am working inside Ktorm with kotlin and MySQL datasource — hanzo2001, May 03 '22 at 15:39
Genius! But would that mean that this is beyond ktorm and all my other tables which are already timestamp are screwed? — hanzo2001, May 03 '22 at 15:52
Yes, if you insist on inserting zero-valued timestamps. I'd recommend using ALTER TABLE and change TIMESTAMP columns to DATETIME. — Bill Karwin, May 03 '22 at 15:56

Cannot insert java.time.LocalDateTime with zero seconds

1 Answers1

Linked