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_TIMESTAMPandON UPDATE CURRENT_TIMESTAMPclauses, the column has the current timestamp for its default value and is automatically updated. - With neither
DEFAULTnorON UPDATEclauses, it is the same asDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. (Only for the first TIMESTAMP field from the table) - With a
DEFAULT CURRENT_TIMESTAMPclause and noON UPDATEclause, the column has the current timestamp for its default value but is not automatically updated. - With no
DEFAULTclause and with anON 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 anON UPDATE CURRENT_TIMESTAMPclause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.
DEFAULT CURRENT_TIMESTAMP in a table.