How can we insert current date and time automatically on inserting values in other columns in MySQL?

MySQLMySQLi Database

In MySQL, we can insert the current date and time automatically to a column on inserting the values in another column by declaring that column as DEFAULT CURRENT_TIMESTAMP.

Example

mysql> Create table testing
   -> (
   -> StudentName varchar(20) NOT NULL,
   -> RegDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   -> );
Query OK, 0 rows affected (0.49 sec)

Above query will create a table ‘testing’ with a column named StudentName and other column named ‘RegDate’ declared as DEFAULT CURRENT_TIMESTAMP. Now, on inserting the values i.e. names in StudentName column, the current date and time will be inserted in the other column automatically.

mysql> Insert into testing(StudentName) values ('Ram');
Query OK, 1 row affected (0.14 sec)

mysql> Insert into testing(StudentName) values ('Shyam');
Query OK, 1 row affected (0.06 sec)

mysql> Select * from testing;
+-------------+---------------------+
| StudentName | RegDate             |
+-------------+---------------------+
| Ram         | 2017-10-28 21:24:24 |
| Shyam       | 2017-10-28 21:24:30 |
+-------------+---------------------+
2 rows in set (0.02 sec)

mysql> Insert into testing(StudentName) values ('Mohan');
Query OK, 1 row affected (0.06 sec)

mysql> Select * from testing;
+-------------+---------------------+
| StudentName | RegDate             |
+-------------+---------------------+
| Ram         | 2017-10-28 21:24:24 |
| Shyam       | 2017-10-28 21:24:30 |
| Mohan       | 2017-10-28 21:24:47 |
+-------------+---------------------+
3 rows in set (0.00 sec)

From the above queries, we can see that on inserting the values in StudentName, the date and time is also inserting automatically.

With the help of the above concept, we can also know that exactly on what date and time the values in other column inserted.

raja
Published on 19-Feb-2018 13:00:24
Advertisements