How to set NOW() as default value for datetime datatype in MySQL?

We can set the now() function as a default value with the help of dynamic default. First, we will create a table with data type” datetime”. After that, we will set now() as the default value for column “MyTime” as shown below.

Creating a table.

mysql> create table DefaultDateTimeDemo
   -> (
   -> MyTime datetime default CURRENT_TIMESTAMP
   -> );
Query OK, 0 rows affected (0.59 sec)

After creating the above table, we won’t insert any value while using the insert command. This is done so that we can get the default date time with the help of dynamic value default.

Here is the query to insert records.

mysql> insert into DefaultDateTimeDemo values();
Query OK, 1 row affected (0.13 sec)

Now, we can check whether the default value now() is added or not. Here is the query to display records.

mysql> select *from DefaultDateTimeDemo;

The following is the output that shows the current date and time.

| MyTime              |
| 2018-11-09 11:58:47 |
1 row in set (0.00 sec)

Now, we can verify whether the result is correct or not with the help of now() method. Here is the query to check the result.

mysql> select now();

The following is the output.

| now()               |
| 2018-11-09 11:58:40 |
1 row in set (0.00 sec)

Look at the sample output above. Both of them gives the same result.

Updated on: 30-Jul-2019

6K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started