How to Insert Timestamp Into a MySQL Table

  2. HowTo
  3. MySQL Howtos
  4. How to Insert Timestamp Into a MySQL …
Mehvish Ashiq Feb 02, 2024
MySQL MySQL Table MySQL Timestamp
  1. Create a MySQL Table
  2. Use NOW() to Insert Timestamp Into a MySQL Table
  3. Use CURRENT_TIMESTAMP() to Insert Timestamp Into a MySQL Table
  4. Set Null or Default Values for the TIMESTAMP Type Column in MySQL Table
How to Insert Timestamp Into a MySQL Table

Today, we will learn how to insert date and time into the TIMESTAMP type column of the MySQL table based on the table definition.

Create a MySQL Table

First, we will create the table that we will be using for this tutorial.

Example 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 know more about the table definition.

Query:

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 don’t have any default values for them. It means we are bound to insert the values for both columns.

Use NOW() to Insert Timestamp Into a MySQL Table

The first approach we will demonstrate to insert TIMESTAMP into a MySQL table is NOW().

Query:

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

Use the SELECT statement to see the current table data.

Query:

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)

Use CURRENT_TIMESTAMP() to Insert Timestamp Into a MySQL Table

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

Query:

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

We can use the SELECT statement as given below to see the table’s data.

Query:

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 Values for the TIMESTAMP Type Column in MySQL Table

If we don’t want to insert the value for the TIMESTAMP type column, we can do that in two ways. Either set that column to accept NULL values or set the default value for the column.

Example code (accept null values for the 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, we can use NULL if we don’t want to insert the value for the demo_two_timestamp column. We also have the flexibility to insert the correct value using NOW() or CURRENT_TIMESTAMP() if we don’t want to have NULL for a specific record.

Example code (set the default value for 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 the value for the demo_three_timestamp column nor wish to have NULL, then we can set the default values using the DEFAULT CURRENT_TIMESTAMP as demonstrated in the above example.

Author: Mehvish Ashiq
Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - MySQL Table

Related Article - MySQL Timestamp