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.
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
ON UPDATE CURRENT_TIMESTAMPclauses, the column has the current timestamp for its default value and is automatically updated.
- With neither
ON UPDATEclauses, 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_TIMESTAMPclause and no
ON UPDATEclause, the column has the current timestamp for its default value but is not automatically updated.
- With no
DEFAULTclause and with an
ON UPDATE CURRENT_TIMESTAMPclause, the column has a default of 0 and is automatically updated.
- With a constant
DEFAULTvalue, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an
ON UPDATE CURRENT_TIMESTAMPclause, 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.