JIYIK CN >

Today, we will learn how to TIMESTAMPinsert date and time into a type column of a MySQL table according to the table definition.

Create a MySQL table

First, we will create the tables that we will use in this tutorial.

Sample code:

CREATE TABLE demo_one(
    demo_one_id INT NOT NULL,
    demo_one_timestamp TIMESTAMP NOT NULL,
    PRIMARY KEY(demo_one_id)
);

Use DESCRIBE as follows to learn more about the table definition.

DESCRIBE demo_one;

Output:

+--------------------+-----------+------+-----+---------+-------+
| Field              | Type      | Null | Key | Default | Extra |
+--------------------+-----------+------+-----+---------+-------+
| demo_one_id        | int       | NO   | PRI | NULL    |       |
| demo_one_timestamp | timestamp | NO   |     | NULL    |       |
+--------------------+-----------+------+-----+---------+-------+
2 rows in set (0.08 sec)

We can see that both attributes do not accept NULL values ​​and we have not set any default values ​​for them. This means we have to insert values ​​for both columns.

Inserting timestamp into MySQL table using NOW()

The first method we will demonstrate for inserting a TIMESTAMP into a MySQL table is NOW().

INSERT INTO demo_one (demo_one_id, demo_one_timestamp)
VALUES
(1, NOW());

Use the SELECT statement to view the current table data.

SELECT * FROM demo_one;

Output:

+-------------+---------------------+
| demo_one_id | demo_one_timestamp  |
+-------------+---------------------+
|           1 | 2022-05-14 11:04:11 |
+-------------+---------------------+
1 row in set (0.00 sec)

Inserting timestamp into MySQL table using CURRENT_TIMESTAMP()

We can also use CURRENT_TIMESTAMP()the method to insert a TIMESTAMP into the MySQL table we created earlier.

INSERT INTO demo_onE (demo_one_id, demo_one_timestamp)
VALUES
(2, CURRENT_TIMESTAMP());

We can view the data of the table using the SELECT statement given below.

SELECT * FROM demo_one;

Output:

+-------------+---------------------+
| demo_one_id | demo_one_timestamp  |
+-------------+---------------------+
|           1 | 2022-05-14 11:04:11 |
|           2 | 2022-05-14 11:06:01 |
+-------------+---------------------+
2 rows in set (0.03 sec)

Set null or default value for TIMESTAMP type column in MySQL table

If we do not want to insert TIMESTAMPa value for a type column, we can do this in two ways. Set the column to accept NULL values ​​or set a default value for the column.

Sample code (accepts null value for demo_two_timestamp field):

CREATE TABLE demo_two(
    demo_two_id INT NOT NULL,
    # we can also write the following column definition
    # as `demo_two_timestamp TIMESTAMP NULL`
    demo_two_timestamp TIMESTAMP,
    PRIMARY KEY(demo_two_id)
);

INSERT INTO demo_two (demo_two_id, demo_two_timestamp) VALUES (1,null), (2, NOW());

SELECT * FROM demo_two;

Output:

+-------------+---------------------+
| demo_two_id | demo_two_timestamp  |
+-------------+---------------------+
|           1 | NULL                |
|           2 | 2022-05-14 11:15:18 |
+-------------+---------------------+
2 rows in set (0.04 sec)

Here, if we don't want to insert demo_two_timestampthe value of the column, we can use NULL. We also have the flexibility to use NOW()or CURRENT_TIMESTAMP()to insert the correct value if we don't want a particular record to be NULL.

Sample code (setting the default value of the demo_three_timestamp field):

CREATE TABLE demo_three(
    demo_three_id INT NOT NULL,
    demo_three_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(demo_three_id)
);

INSERT INTO demo_three (demo_three_id) VALUES (1);
INSERT INTO demo_three (demo_three_id, demo_three_timestamp) VALUES (2, NOW());
INSERT INTO demo_three (demo_three_id, demo_three_timestamp)
VALUES (3, CURRENT_TIMESTAMP());

SELECT * FROM demo_three;

Output:

+---------------+----------------------+
| demo_three_id | demo_three_timestamp |
+---------------+----------------------+
|             1 | 2022-05-14 11:21:57  |
|             2 | 2022-05-14 11:22:20  |
|             3 | 2022-05-14 11:22:40  |
+---------------+----------------------+
3 rows in set (0.00 sec)

If we neither want to enter demo_three_timestampa value for the column nor want to have NULLs, then we can use DEFAULT CURRENT_TIMESTAMPset a default value as shown in the above example.