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.
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image.png)
When I originally set this column up, I set the default to CURRENT_TIMESTAMP
. Here is what it looks like in phyMyAdmin:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-1-1024x160.png)
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.
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-2-1024x225.png)
Which then loads the details for this column:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-3-1024x106.png)
I then changed the Type from DATETIME
to TIMESTAMP
.
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-4-1024x734.png)
And I changed the Attributes from blank to “on update CURRENT_TIMESTAMP”. For example:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-5.png)
Then on the right side of the page, I clicked the “Preview SQL” button:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-7.png)
Which made this small box appear with the SQL that is being executed:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-8.png)
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:
![](https://bluegalaxy.info/codewalk/wp-content/uploads/2020/09/image-9-1024x225.png)