I have a table in my database called “usercardsetplaycounts” that has a column called “updatedAt” that I want to get updated with a fresh timestamp every time a row is edited.
When I originally set this column up, I set the default to
CURRENT_TIMESTAMP. Here is what it looks like in phyMyAdmin:
The updatedAt date is set to the timestamp of when the table was created, and does not change when rows are edited.
To fix this, I went to the Structure view in phpMyAdmin and clicked the “Change” link in the Action column.
Which then loads the details for this column:
I then changed the Type from
And I changed the Attributes from blank to “on update CURRENT_TIMESTAMP”. For example:
Then on the right side of the page, I clicked the “Preview SQL” button:
Which made this small box appear with the SQL that is being executed:
The actual SQL code for this table update is:
ALTER TABLE `usercardsetplaycounts` CHANGE `updatedAt` `updatedAt` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
After closing this dialog, all I had to do was click the “Save” button to make this change to the database. Now whenever any row in this table is updated, the updatedAt column will contain a fresh timestamp for that row. Here is what the Structure looks like after the change: