MySQL: Write current Date or Time into Column

Often, you have to write the current date or the current time into a MySQL table. For example, when it comes to log the last login time or something like this.

Of course, you could also prepare the date or time entry using PHP, but MySQL already provides some useful and much easier to use functions that we can use in this context.

In this article we would like to look at the following application examples regarding this functionality:

• Insert Date with Time
• Insert Date or Time
• Insert Date and Time automatically
• Update Date and Time automatically

Insert Date with Time

If we want to insert the date and the time, for example, in a TIMESTAMP or DATETIME column, we can simply use the NOW() function. For example, in this way:

UPDATE tab SET col_timestamp = NOW();

NOW() automatically ensures that the current date with the time will be entered, so for example "2022-12-18 11:33:25".

Insert Date or Time

If we would like to enter only the date or only the time, we can use the functions CURDATE() and CURTIME() for that. Here is an example for the use of both functions:

UPDATE tab SET col_onlydate = CURDATE();
UPDATE tab SET col_onlytime = CURTIME();

CURDATE() would insert "2022-12-18", for example, and CURTIME() for instance "11:33:25". As a data type for these two columns, you could use the types DATE or TIME  that, in contrast to TIMESTAMP and DATETIME, only contain the date part or the time part.

Insert Date and Time automatically

In some cases it is even easier and we can leave MySQL the complete work. For example, if the current date and the current time are to be entered automatically into a column when creating a new data record. The only thing to do in this case is the following definition for the relevant column:

col_auto_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP

By setting the default value of a TIMESTAMP column or a DATETIME column to "CURRENT_TIMESTAMP", we no longer have to worry about setting the timestamps. Every time a new data record is added to the table, the column in question receives the value of the current date including the current time automatically with this. This function can be useful, for example, for log entries or for storing the comments of a comment function that are to be logged with the current date.

Update Date and Time automatically

MySQL not only offers us the opportunity to automatically set the current date and the current time when inserting data records, but also when updating a data record. In this case too, we just need to adapt the column definition and have 2 options:

col_auto_update TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

With this column definition, the value of the column is first "0" after inserting. Only with the first update, the value of the column is set to the current date and time.

If we would like to set the value of the column to the current time already when inserting it, we can simply set the default value from "0" to "CURRENT_TIMESTAMP" and receive:

col_auto_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

With this column definition, the value of the column is set directly during creation to the current date and the current time. With each update of the corresponding data record, the value is updated and thus logs the last change of the data record. You could use this function, for example, in a blog in which not only the creation date for the articles should be displayed but also the last change date, as it is also the case with the topics and comments on AskingBox.

However, caution is required when using this function and the data record also includes data whose update should not update the time. One example would be the storage of the clicks of a web page. If those are stored together with the text of the page within one data record, one certainly does not want that the change date of the data record is updated with each update of the number of views. In this case, we have to use the NOW() function presented first to be able to only perform an update manually if it is desired.