Introduction to MySQL DATETIME Data Type

The DATETIME data type in MySQL is a date and time format that can store dates and times between ‘1000-01-01 00:00:00’ and ‘9999-12-31 23:59:59’. Its storage size is 8 bytes.

Syntax

The syntax for the DATETIME data type is as follows:

DATETIME[(<fsp>)]

where <fsp> represents fractional seconds precision, which can be any value from 0 to 6. If fractional seconds precision is not specified, it defaults to 0.

Use Cases

The DATETIME data type is commonly used for storing data with date and time information. It can be used for storing timestamps, event times, and other similar information.

Examples

Here are two examples of using the DATETIME data type:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME
);

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1, 101, '2022-01-01 10:30:00');

SELECT * FROM orders WHERE order_date > '2022-01-01 00:00:00';

The above example creates a table named orders with three columns: order_id, customer_id, and order_date. order_id is an integer and serves as the primary key. customer_id is also an integer, representing the customer’s ID. order_date is of DATETIME type, used to store the date and time of the order.

A row of data is inserted into the orders table, and then a query is executed to retrieve all orders placed after ‘2022-01-01 00:00:00’. The query will return the order that was inserted above, as it falls after the specified date and time.

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(50),
    start_time DATETIME,
    end_time DATETIME
);

INSERT INTO events (event_id, event_name, start_time, end_time)
VALUES (1, 'New Year''s Eve Party', '2022-12-31 20:00:00', '2023-01-01 02:00:00');

SELECT * FROM events WHERE start_time BETWEEN '2022-12-31 00:00:00' AND '2022-12-31 23:59:59';

The above example creates a table named events with four columns: event_id, event_name, start_time, and end_time. event_id is an integer and serves as the primary key. event_name is a string, representing the name of the event. start_time and end_time are both of DATETIME type, used to store the start and end dates and times of the event.

A row of data is inserted into the events table, and then a query is executed to retrieve all events that occurred between 8:00 PM on December 31, 2022 and 2:00 AM on January 1, 2023. The query will return a result set with one row, which contains the New Year’s Eve Party event.

Conclusion

The DATETIME data type in MySQL is a data type used for storing dates and times, capable of storing dates and times from the year 1000 to the year 9999. Compared to other date and time data types, it has higher precision and larger storage capacity, but also requires more storage space. When using the DATETIME data type, it is important to be mindful of its format and range limitations to avoid errors in data storage and processing.

In practical applications, the DATETIME data type is commonly used to store time-related data, such as article publication time, order creation time, user registration time, and so on. By applying the DATETIME data type flexibly, it can improve efficiency and accuracy in data storage and processing.