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.
PHPMyAdmin table creation interface
Once created, table structure should be similar to below screenshot.
Created table structure
Give it a try by creating a new table entry
New table entry insertion
In the browsing interface, see your entry newly created – both creation and modification dates are equal.
Table entry created
Try updating one of the values of one 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.
Entry modified and modification timestamp updated accordingly
It is of course also possible to do it using SQL, here’s the corresponding code :
CREATETABLE `table_test` (
`ID` int(11) NOTNULL,
`Description` varchar(255) NOTNULL,
`CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ModificationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDBDEFAULTCHARSET=utf8 COLLATE=utf8_bin;