MySQL DATETIME

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.