In MySQL, how can I insert date and time automatically while inserting NULL values to the other columns?

In MySQL, we can insert current date and time automatically to a column on inserting the NULL values in other columns by declaring that column as DEFAULT CURRENT_TIMESTAMP. In this case, we cannot declare the column NOT NULL in which we want to insert NULL values.

mysql> Create Table Testing1(Name Varchar(20),
RegStudent TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.15 sec)

Above query will create a table ‘Testing1’ with a column named ‘Name’(not declared ‘NOT NULL’) and other column named ‘RegDate’ declared as DEFAULT CURRENT_TIMESTAMP. Now, on inserting the NULL values Name column, the current date and time will be inserted in the other column automatically.

mysql> Insert into Testing1(Name) Values(NULL);
Query OK, 1 row affected (0.08 sec)

mysql> Insert into Testing1(Name) Values(NULL);
Query OK, 1 row affected (0.04 sec)

mysql> Select * from Testing1;
+------+---------------------+
| Name | RegStudent |
+------+---------------------+
| NULL | 2017-10-29 04:46:59 |
| NULL | 2017-10-29 04:47:02 |
+------+---------------------+
2 rows in set (0.05 sec)

From the above queries, we can see that on inserting the NULL values in ‘Name’, the date and time are also inserting automatically.

Krantik Chavan

e

Updated on: 29-Jan-2020

714 Views

Kickstart Your Career

Get certified by completing the course

Get Started