MySQL Insert DateTime

Summary: in this tutorial, you will learn how to insert DATETIME values into a table in MySQL database.

Defining a DATETIME column

The DATETIME data type is used to store both date and time values. To define a column with the DATETIME data type, you use the following syntax:

column_name DATETIMECode language: SQL (Structured Query Language) (sql)

In practice, you use the DATETIME columns to store both date and time values such as event times, logging times, and more.

To insert data into the DATETIME columns, you need to ensure that the datetime values are in the 'YYYY-MM-DD HH:MM:SS' format.

If you have datetime values in different formats, you need to format them to match the 'YYYY-MM-DD HH:MM:SS' expected by MySQL.

We’ll show you some examples of inserting data into the DATETIME column.

Inserting a datetime value example

First, create a table called events:

CREATE TABLE events(
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    event_time DATETIME NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The events table has three columns:

• id: This is an auto-increment primary key column.
• event_name: This column stores the name of the event.
• event_time: This column has the DATETIME data type that stores the event’s date and time.

Second, insert a new row into the events table and use the datetime format 'YYYY-MM-DD HH:MM:SS':

INSERT INTO events(event_name, event_time)
VALUES('MySQL tutorial livestream', '2023-10-28 19:30:35');Code language: SQL (Structured Query Language) (sql)

Third, query data from the events table:

SELECT * FROM events;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------------------------+---------------------+
| id | event_name                | event_time          |
+----+---------------------------+---------------------+
|  1 | MySQL tutorial livestream | 2023-10-28 19:30:35 |
+----+---------------------------+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Inserting the current datetime

To insert the current date and time into a DATETIME column, you use the NOW() function as the datetime value. For example:

INSERT INTO events(event_name, event_time)
VALUES('MySQL Workshop', NOW());Code language: SQL (Structured Query Language) (sql)

In this example, we use the NOW() function to get the current datetime value and use it to insert it into the event_time column of the events table.

Inserting a datetime string example

If you want to insert a datetime string into a DATETIME column, you need to use the STR_TO_DATE() function to convert it to an expected format. For example:

INSERT INTO events (event_name, event_time)
VALUES ('MySQL Party', STR_TO_DATE('10/28/2023 20:00:00', '%m/%d/%Y %H:%i:%s'));Code language: JavaScript (javascript)

In this example, we use the STR_TO_DATE() function to convert the datetime string '10/28/2023 20:00:00' to the expected format.

Summary

• Use the datetime value with the format 'YYYY-MM-DD HH:MM:SS' to insert into a DATETIME column.