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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>