The DATETIME data type in MySQL is used to store date and time values in a combined format. It is a fundamental data type that allows you to represent a specific point in time with both date and time components. The DATETIME format is ‘YYYY-MM-DD HH:MM:SS’, where:
YYYY represents the four-digit year.
MM represents the two-digit month (01 to 12).
DD represents the two-digit day (01 to 31).
HH represents the two-digit hour (00 to 23).
MM represents the two-digit minute (00 to 59).
SS represents the two-digit second (00 to 59).
For example, a DATETIME value may look like ‘2023-12-26 15:30:45’, representing December 26, 2023, at 3:30:45 PM.
Example
Here’s an example of how to create a table with a DATETIME column in MySQL:
CREATE TABLE example_table ( id INT PRIMARY KEY, event_datetime DATETIME );
You can insert data into this table using the DATETIME format:
INSERT INTO example_table (id, event_datetime) VALUES (1, '2023-12-26 15:30:45');
MySQL provides various functions to work with DATETIME values, allowing you to perform operations like extracting components, formatting, and manipulating dates and times. Some commonly used functions include:
NOW(): Returns the current date and time.
CURDATE(): Returns the current date.
CURTIME(): Returns the current time.
DATE_FORMAT(): Formats a date as specified.
Here’s an example of using the NOW() function:
SELECT NOW();
MySQL also supports arithmetic operations on DATETIME values, making it possible to add or subtract intervals. For instance, to add one day to a DATETIME value, you can use the DATE_ADD() function:
SELECT DATE_ADD(event_datetime, INTERVAL 1 DAY) FROM example_table;
It’s important to note that DATETIME values in MySQL are limited to the range ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’, and they include fractional seconds to microseconds precision.
In summary, the DATETIME data type in MySQL is a versatile and widely used option for storing date and time information in a format that allows for easy manipulation and retrieval of temporal data.