Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin

Quick links

Creating fields with time stamps that contains the creation and / or the last modification dates in MySQL using PHPMyAdmin is straightforward.

No need for specific SQL code in latest versions, as it is now possible to have two different fields in the same table with current timestamp as default, and therefore no need to use triggers anymore for this purpose.

In the table creation interface, add two fields – one for the creation date, and one for the modification – with options as described : both as timestamp, current timestamp as default value, and attribute “on update” for the last modification date.

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : PHPMyAdmin table creation interface
PHPMyAdmin table creation interface

Once created, table structure should be similar to below screenshot.

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : Created table structure
Created table structure

Give it a try by creating a new table entry :

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : New table entry insertion
New table entry insertion

In the browsing interface, see your entry newly created – both creation and modification dates are equal.

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : Table entry created
Table entry created

Try updating one of the values of one entry

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : Modification of a table entry
Modification of a table entry

And see directly the change : modification date has been updated to match change time, and creation time hasn’t.

Create a MySQL table with automatic creation date and modification date fields in PHPMyAdmin : Entry modified and modification timestamp updated accordingly
Entry modified and modification timestamp updated accordingly

It is of course also possible to do it using SQL, here’s the corresponding code :

CREATE TABLE `table_test` (
`ID` int(11) NOT NULL,
`Description` varchar(255) NOT NULL,
`CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ModificationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Similar articles

Comments (8)

Rudolph Henry, 2018-08-19
Lo pasé muy bien leyendo esta información, sigan con el buen trabajo
Kay Chapman, 2018-08-19
Grazie è stato utile
Kate Schultz, 2018-08-19
簡單,清晰,解釋得很好,非常感謝
Amy Coleman, 2018-08-19
簡直不敢相信我終於找到了解決方案,這是一場很長一段時間的噩夢,現在已經解決了
Ann Perez, 2018-08-19
ไซต์ที่เยี่ยมยอดโปรดไปต่อ
Eric Parker, 2018-08-19
Sonunda çözümü bulduğuma inanamıyorum, bu uzun zamandan beri bir kabustu, şimdi çözüldü
Janice Perry, 2018-08-19
Привет, я видел вашу статью, и это помогло мне решить мою проблему, спасибо большое
dias2zl, 2018-08-19
Tidak tahu bagaimana untuk mengucapkan terima kasih, tetapi itu adalah jenius murni, terima kasih

Leave a comment