Date of records in MySQL
When a database is planned is often useful, if not quite indispensable, to make available the date of creation and last modification of a record.
If you realize this need when your tables have just pupulated with hundreads of records, you’re in trouble. It is good therefore to always put these dates, also when they seem useless information. Their insertion and maintenance involve very little effort but protect us from unexpected needs (our or of the customer).
Take as example a table with a structure like this:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`creation` datetime NOT NULL default '0000-00-00 00:00:00',
`last` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
);
In the creation field will be stored the cration date of the record and in the last filed will be stored the last modificatio date.
The only time we must to worry about setting a date is in the creation of a record, to set the creation field. It will remain the same value for all the record’s life. The code to insert a new record:
INSERT INTO test (name, creation)
VALUES ('John', NOW( ))";
The NOW( ) function returns the current date-time.
Just after the record creation, creation and last fileds are equal. The last field will be automatically updated every time a record is updated. You don’t need to set it, it’s a MySQL job. So, in the next update of the record we don’t care to set values for this two fields:
UPDATE test
SET nome='Robert'
WHERE id=1;
As a result the creation field remain the same and last will be set to the date-time of the issued UPDATE.
Note: the dates in the datetime and timestamp kind of fields have the same format, that is YYYY-MM-DD HH:mm:ss.
