mybatis DateTypeHandler cannot convert java Date to sql TIMESTAMP

Question

I want store Date as TIMESTAMP in MySQL. I found DateTypeHandler seems can do this conversion. I put it in my project, but it failed.

I have tried both of the following in my "mapper.xml", they both failed.

<insert id="insertUser">
    insert into user (user_id, password, token,
    register_time, user_type, account_type) values (
    #{userId},
    #{password}, #{token}, #{registerTime, jdbcType=TIMESTAMP,
    javaType=java.util.Date, typeHandler=org.apache.ibatis.type.DateTypeHandler},
    #{userType},
    #{accountType}
    )
</insert>

<insert id="insertUser">
    insert into user (user_id, password, token,
    register_time, user_type, account_type) values (
    #{userId},
    #{password}, #{token}, #{registerTime, typeHandler=org.apache.ibatis.type.DateTypeHandler},
    #{userType},
    #{accountType}
    )
</insert>

The error log is as following:

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.jdbc.MysqlDataTruncation: 
Data truncation: Incorrect datetime value: '3892-04-19 00:00:00' for column 
'register_time' at row 1
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into user (user_id, password, token,   register_time, user_type, account_type) values (   ?,   ?, ?, ?,   ?,   ?   )
### Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect 
datetime value: '3892-04-19 00:00:00' for column 'register_time' at row 1
; SQL []; Data truncation: Incorrect datetime value: '3892-04-19 00:00:00' 
for column 'register_time' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: org.springframework.dao.DataIntegrityViolationException: 

Answer 1

MySql TIMESTAMP type only can store data values in a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC, and you try to insert value '3892-04-19 00:00:00' in register_time column which is of this type. This throws the error:

Data truncation: Incorrect datetime value: '3892-04-19 00:00:00' for column register_time' at row 1

Check if this future date is correct and if it is so, change jdbcType=TIMESTAMP, to jdbcType=DATETIME in your mapper.xml can resolve the issue. (MySql DATETIME type supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

References: MySql doc