How do you insert a timestamp in MySQL?

---

You can use TIMESTAMP to insert a timestamp column into a table in MySQL.

When inserting timestamps, they must be in the following format:

'YYYY-MM-DD HH:MM:SS'

where:

• YYYY: The year in four digits
• MM: The month in two digits
• DD: The day in two digits
• HH: The hours in two digits
• MM: The minutes in two digits
• SS: The seconds in two digits

Note that you can also include trailing fractional seconds up to 6 digits in a timestamp.

For example, ‘2022-10-28 04:15:56.003495‘ is a valid timestamp.

Also note that TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

The following example shows how to insert a timestamp column into a table in MySQL in practice.

Example: How to Insert Timestamp in MySQL

We can use the following syntax to create a table named sales that contains information about sales made at various grocery stores at various times:

-- create table 
CREATE TABLE sales (
  store_ID INT PRIMARY KEY,
  item TEXT NOT NULL,
  sales_time TIMESTAMP NOT NULL
);

-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2015-01-12 03:45:00');
INSERT INTO sales VALUES (0002, 'Apples', '2020-11-25 15:25:01');
INSERT INTO sales VALUES (0003, 'Bananas', '2009-06-30 09:01:39');
INSERT INTO sales VALUES (0004, 'Melons', '2022-04-09 03:29:55');
INSERT INTO sales VALUES (0005, 'Grapes', '2023-05-19 23:10:04');

-- view all rows in table
SELECT * FROM athletes;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_time          |
+----------+---------+---------------------+
|        1 | Oranges | 2015-01-12 03:45:00 |
|        2 | Apples  | 2020-11-25 15:25:01 |
|        3 | Bananas | 2009-06-30 09:01:39 |
|        4 | Melons  | 2022-04-09 03:29:55 |
|        5 | Grapes  | 2023-05-19 23:10:04 |
+----------+---------+---------------------+

Notice that the sales_time column is a timestamp column where each of the timestamps are formatted as YYYY-MM-DD HH:MM:SS.

If you attempt to insert a timestamp in a different format, you will receive an error:

-- insert row into table
INSERT INTO sales VALUES (0006, 'Cabbage', '7/22/2023 05:56:00');

-- view all rows in table
SELECT * FROM sales;

ERROR 1292 (22007): Incorrect datetime value: '7/22/2023 05:56:00' for column 'sales_time' at row 1

If you would like to insert a timestamp into the timestamp column in a different format, you can use STR_TO_DATE to do so:

-- insert row into table
INSERT INTO sales VALUES (0006, 'Cabbage', STR_TO_DATE('7/22/2023 05:56:00', '%m/%d/%Y %H:%i:%s'));

-- view all rows in table
SELECT * FROM sales;

Output:

+----------+---------+---------------------+
| store_ID | item    | sales_time          |
+----------+---------+---------------------+
|        1 | Oranges | 2015-01-12 03:45:00 |
|        2 | Apples  | 2020-11-25 15:25:01 |
|        3 | Bananas | 2009-06-30 09:01:39 |
|        4 | Melons  | 2022-04-09 03:29:55 |
|        5 | Grapes  | 2023-05-19 23:10:04 |
|        6 | Cabbage | 2023-07-22 05:56:00 |
+----------+---------+---------------------+

By using STR_TO_DATE, we are able to insert this new timestamp value without any errors.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Insert Datetime