How to prevent off-rounding timestamp in MySQL

Question

I have the issue that a field called updated_at which the user freely selects a date to be stored, that if the user send 2021-08-11T21:59:59.999Z, this will be rounded to 2021-08-11 22:00:00.0 automatically, so the field is looking like this

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "updated_at")
private Date updatedAt;

I read that changing the timestamp to timestamp(3) will solve the rounding problem, so I wrote a Liquibase script like this changeset

<changeSet id="10" author="autho2">
     <modifyDataType
             columnName="updated_at"
             newDataType="timestamp(3)"
             tableName="user_data"/>
 </changeSet>

However, I get error:

 liquibase.exception.DatabaseException: 
Invalid default value for 'updated_at' [Failed SQL:
 (1067) ALTER TABLE user_data MODIFY updated_at timestamp(3)]

What am I doing wrong or am i converting date to instance wrongly?

Does this answer your question? [ERROR 1067 (42000): Invalid default value for 'created\_at'](https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at) — Simon Martinelli, Jul 02 '21 at 12:54
this is more generic answer, i want a specific answer based on my case — Catalina, Jul 02 '21 at 12:56
Never use either `Date` class. They were years ago supplanted by the modern *java.time* classes defined in JSR 310. — Basil Bourque, Jul 02 '21 at 22:36
i am using instance, see update post @BasilBourque convert method — Catalina, Jul 04 '21 at 15:07
@SimonMartinelli how can i change the mysql mode in liquibase? — Catalina, Jul 05 '21 at 17:35
Did you change versions of MySQL at about the same time? There have been changes in the definition of `TIMESTAMP` -- especially when it comes to defaults. — Rick James, Jul 07 '21 at 17:30

score 4 · Answer 1 · answered Jul 04 '21 at 17:51

4

When MODIFYing a column definition, you must include all attributes that you want to keep:

ALTER TABLE user_data
    MODIFY updated_at timestamp(3) 
    NULL                           -- if you you want it NULLable
    DEFAULT CURRENT_TIMESTAMP(3)   -- note this needs "(3)" too
    ON UPDATE CURRENT_TIMESTAMP(3) -- if you want

answered Jul 04 '21 at 17:51

Rick James

135,179
13
127
222

could you please show me how that should be written as liquibase script? – Catalina Jul 04 '21 at 17:57

score 2 · Answer 2 · answered Jul 09 '21 at 06:02

Adding on to Rick James's answer: (Rick's SQL works like a charm)

To use it with Liquibase, you may try using :

1. <changeSet id="10" author="autho2">
     <sql>
         ALTER TABLE user_data
         MODIFY updated_at timestamp(3) 
         NULL                           -- if you you want it NULLable
         DEFAULT CURRENT_TIMESTAMP(3)   -- note this needs "(3)" too
         ON UPDATE CURRENT_TIMESTAMP(3); -- if you want
     <sql>
   </changeSet>

2. <changeSet id="10" author="autho2">
     <modifyDataType
             columnName="updated_at"
             newDataType="timestamp(3)"
             tableName="user_data"/>
     <modifySql>  
             <append  value="NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)"/>
        </modifySql>
   </changeSet>

Before running liquibase update you can try using liquibase updateSQL and check the output SQL, if is the expected SQL you are going to run.

How to prevent off-rounding timestamp in MySQL

2 Answers2