Can MySQL automatically store timestamp in a row?

MySQLMySQLi Database

Yes, you can achieve this in the following two ways.

  • First Approach At the time of creation of a table.

  • Second Approach At the time of writing query.

The syntax is as follows.

CREATE TABLE yourTableName
(
yourDateTimeColumnName datetime default current_timestamp
);

You can use alter command.

The syntax is as follows.

ALTER TABLE yourTableName ADD yourColumnName datetime DEFAULT CURRENT_TIMESTAMP;

Implement both the syntaxes now.

The first approach is as follows.

mysql> create table CurrentTimeStampDemo
-> (
-> CreationDate datetime default current_timestamp
-> );
Query OK, 0 rows affected (0.61 sec)

If you do not pass any parameter for the column ‘CreationDate’, MySQL by default stores the current timestamp.

Insert record in the table using insert command. The query is as follows.

mysql> insert into CurrentTimeStampDemo values();
Query OK, 1 row affected (0.12 sec)
Let us now display all records from the table

using select command. The query is as follows.

mysql> select *from CurrentTimeStampDemo;

The following is the output.

+---------------------+
| CreationDate        |
+---------------------+
| 2019-01-02 15:53:03 |
+---------------------+
1 row in set (0.00 sec)

Second Approach

This is using ALTER command. The query to create a table is as follows.

mysql> create table Current_TimestampDemo
-> (
-> Id int
-> );
Query OK, 0 rows affected (0.43 sec)

Here is the query to automatically store creation time. The query is as follows.

mysql> ALTER TABLE Current_TimestampDemo
-> ADD CreationDate datetime DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0

If you do not pass the value for column ‘CreationDate’. MySQL stores the current datetime value for this column. The query is as follows.

mysql> insert into Current_TimestampDemo(Id) values(1);
Query OK, 1 row affected (0.19 sec)

Display records of the table using select statement. The query is as follows.

mysql> select CreationDate from Current_TimestampDemo;

The following is the output.

+---------------------+
| CreationDate        |
+---------------------+
| 2019-01-02 16:25:12 |
+---------------------+
1 row in set (0.00 sec)
raja
Published on 11-Jan-2019 11:37:22
Advertisements