Search code examples
mysqlsqlsql-timestamp

MySQL: Multiple Timestamps in One Table for DateAdded, DateUpdated


I want to have multiple Timestamps in one table for columns DateAdded and DateUpdated. I have this incorrect definition where DateAdded is at '0000-00-00 00:00:00' and only works for DateUpdated.

`DateAdded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`DateUpdated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

When I try to edit to have 2 Timestamps I get this error:

 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Please help me with correct workaround. I can see the issue is known but I am not seeing a solution anywhere.


Solution

  • I would replace invalid timestamp:

    ALTER TABLE tab MODIFY `DateAdded` timestamp DEFAULT CURRENT_TIMESTAMP;
                                                         -- or any other valid value
    

    DBFiddle Demo