Table of Contents
Datetime can be inserted in MySQL using the INSERT command, which allows users to add data into a specific table and column in the database. An example of inserting datetime in MySQL would be inserting the current date and time into a column called “created_at” in a table called “users”. This can be done using the following query: INSERT INTO users (created_at) VALUES (NOW()); This will automatically insert the current datetime into the “created_at” column for each new row added to the “users” table.
You can use DATETIME to insert a datetime column into a table in MySQL.
When inserting datetimes, 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
The following example shows how to insert a datetime column into a table in MySQL in practice.
Example: How to Insert Datetime 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 DATETIME 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 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 |
+----------+---------+---------------------+ Notice that the sales_time column is a datetime column where each of the datetimes are formatted as YYYY-MM-DD HH:MM:SS.
If you attempt to insert a datetime in a different format, you will receive an error:
-- insert row into table
INSERT INTO sales VALUES (0006, 'Pears', '5/18/2023 05:56:00');
-- view all rows in table
SELECT * FROM sales; Output:
ERROR 1292 (22007): Incorrect datetime value: '5/18/2023 05:56:00' for column 'sales_time' at row 1
If you would like to insert a datetime into the datetime column in a different format, you can use STR_TO_DATE to do so:
-- insert row into table
INSERT INTO sales VALUES (0006, 'Pears', STR_TO_DATE('5/18/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 | Pears | 2023-05-18 05:56:00 |
+----------+---------+---------------------+ By using STR_TO_DATE, we are able to insert this new datetime value without any errors.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: