Why use CURRENT_TIMESTAMP on a field that record date/time?

On a TIMESTAMP field that records date and time when inserting a new record, it is encouraged to use as a DEFAULT value, the CURRENT_TIMESTAMP constant. Why? Because when inserting a new row in the table for the date and time field there is no need to specifically add its value, either by creating it from PHP code with the Date/ Time functions or with MySQL function NOW()

ALTER TABLE `user` CHANGE `dateCreated` `dateCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP is also a solution for updating date and time fields. Use ON UPDATE CURRENT_TIMESTAMP clause, if you want the value of the field to be changed automatically each time the row is updated.

ALTER TABLE `user` CHANGE `dateLogin` `dateLogin` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

DEFAULT and ON UPDATE clauses can be used together or separately, depending on your needs:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value and is automatically updated.
With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. (Only for the first TIMESTAMP field from the table)
With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.

For more details check out MySQL Manual Note*: Only one timestamp field can be DEFAULT CURRENT_TIMESTAMP in a table.

Frequently Asked Questions

Why use CURRENT_TIMESTAMP as a DEFAULT value for a date/time field? +

Because when inserting a new row, there is no need to specifically set the date/time value yourself, either from PHP Date/Time functions or with MySQL's NOW() function.

How do you make a field update its timestamp automatically on every UPDATE? +

Add the ON UPDATE CURRENT_TIMESTAMP clause, for example: ALTER TABLE `user` CHANGE `dateLogin` `dateLogin` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.

What happens if a TIMESTAMP column has neither a DEFAULT nor an ON UPDATE clause? +

For the first TIMESTAMP field in the table, having neither clause is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

What happens with a DEFAULT CURRENT_TIMESTAMP clause but no ON UPDATE clause? +

The column gets the current timestamp as its default value but is not automatically updated afterward.

Can more than one TIMESTAMP column default to CURRENT_TIMESTAMP in the same table? +

No. Only one timestamp field in a table can be DEFAULT CURRENT_TIMESTAMP.