How to Set Default Value for Datetime Column in MySQL

MySQL is a popular database used by many websites and organizations. It supports wide range of data formats including date, time, datetime and timestamp data types. Sometimes you may need to set default value for datetime column in MySQL. Since MySQL 5.6.5, you can do this for datetime fields, but not other fields. Earlier, even that was not possible. In this article, we will learn how to set default value for datetime column in MySQL.

How to Set Default Value for Datetime Column in MySQL

Let us say you create the following MySQL database table. In the table definition we have defined a timestamp column ts and set its default value as current timestamp.

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+

Once you have created the above table, you can insert rows into it such that we do not specify the value for timestamp column.

mysql> insert into test (str) values ("demo");

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2022-08-16 22:59:52 | 
+------+---------------------+

You will see that the timestamp column ts has been automatically populated with the current datetime value. Please note, if you have defined column to contain current timestamp, then whenever you update the table, you need to specify the column value for the datetime/timestamp column as column_name=column_name or some other value, else it will automatically update this value to latest timestamp.

In this article, we have learnt how to set default value for datetime column in MySQL

Also read:
How to Empty Array in JavaScript
How to Set Query Timeout in MySQL
How to Count Frequency of Items in JS Array
How to Allow Only Alphabet Input in HTML Text Input
How to Allow Only Numeric Input in HTML Text Input

Sreeram Sreenivasan

Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.