MySql - Date and Time data types

Date and Time Data Types (DATE, DATETIME, TIMESTAMP, etc.) in MySQL 

MySQL provides a variety of data types specifically designed for storing and manipulating date and time values. These data types are essential for applications involving logs, events, time series data, and scheduling. This guide explores MySQL's date and time data types in detail, including DATE, DATETIME, TIMESTAMP, TIME, and YEAR. It also discusses formatting, time zone management, arithmetic, and functions that assist in date/time manipulation.

1. Overview of MySQL Date and Time Data Types

2. DATE Data Type

The DATE type is used to store only date values in the format YYYY-MM-DD.

2.1 Example: Creating a Table with DATE

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    hire_date DATE
);

2.2 Inserting DATE Values

INSERT INTO employees (id, name, hire_date)
VALUES (1, 'Alice', '2024-05-01');

2.3 Retrieving DATE Values

SELECT name, hire_date FROM employees;

3. DATETIME Data Type

DATETIME is used to store both date and time components in the format YYYY-MM-DD HH:MM:SS.

3.1 Creating a Table with DATETIME

CREATE TABLE appointments (
    id INT,
    title VARCHAR(255),
    appointment_time DATETIME
);

3.2 Inserting a DATETIME Value

INSERT INTO appointments (id, title, appointment_time)
VALUES (1, 'Dental Checkup', '2024-07-12 14:30:00');

3.3 Querying DATETIME

SELECT * FROM appointments
WHERE appointment_time BETWEEN '2024-07-01 00:00:00' AND '2024-07-31 23:59:59';

4. TIMESTAMP Data Type

The TIMESTAMP data type is similar to DATETIME but is stored relative to UTC and affected by the time zone setting of the server or client.

4.1 Creating a Table with TIMESTAMP

CREATE TABLE log_events (
    id INT,
    event_description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.2 Automatic Updates

CREATE TABLE documents (
    id INT,
    title VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4.3 Inserting and Retrieving TIMESTAMP

INSERT INTO log_events (id, event_description)
VALUES (1, 'Server started');

SELECT * FROM log_events;

5. TIME Data Type

The TIME type is used to store time intervals or times of day in HH:MM:SS format.

5.1 Creating a Table with TIME

CREATE TABLE work_shifts (
    id INT,
    shift_name VARCHAR(50),
    duration TIME
);

5.2 Inserting TIME Values

INSERT INTO work_shifts (id, shift_name, duration)
VALUES (1, 'Morning Shift', '08:00:00');

6. YEAR Data Type

Stores a four-digit year value.

6.1 Creating a Table with YEAR

CREATE TABLE car_models (
    id INT,
    model_name VARCHAR(50),
    model_year YEAR
);

6.2 Inserting a YEAR Value

INSERT INTO car_models (id, model_name, model_year)
VALUES (1, 'Tesla Model S', 2025);

7. Functions for Date and Time Manipulation

7.1 CURRENT_DATE, CURRENT_TIME, NOW()

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT NOW();

7.2 DATE_ADD and DATE_SUB

SELECT DATE_ADD('2025-01-01', INTERVAL 10 DAY);
SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH);

7.3 DATEDIFF and TIMEDIFF

SELECT DATEDIFF('2025-01-10', '2025-01-01');
SELECT TIMEDIFF('12:30:00', '08:00:00');

7.4 EXTRACT and DATE_FORMAT

SELECT EXTRACT(YEAR FROM '2025-07-12');
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');

7.5 STR_TO_DATE and TIME_FORMAT

SELECT STR_TO_DATE('12-07-2025', '%d-%m-%Y');
SELECT TIME_FORMAT('13:45:00', '%h:%i %p');

8. Comparing DATETIME and TIMESTAMP

8.1 Storage and Range

• DATETIME has a wider range but uses more storage.
• TIMESTAMP uses less storage and is suitable for tracking events based on UTC.

8.2 Time Zone Behavior

• DATETIME is not affected by time zone settings.
• TIMESTAMP automatically adjusts based on the session or server time zone.

9. Time Zones and MySQL

9.1 Checking and Setting Time Zones

SELECT @@global.time_zone;
SET time_zone = '+05:30';

9.2 Using UTC_TIMESTAMP

SELECT UTC_TIMESTAMP();

10. Indexing and Date Performance Tips

• Create indexes on date columns used in WHERE or JOIN conditions.
• Use date range filters instead of functions on date columns for better performance.

10.1 Avoid

SELECT * FROM orders WHERE YEAR(order_date) = 2025;

10.2 Prefer

SELECT * FROM orders 
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

11. Practical Examples

11.1 Users Registered in the Last 7 Days

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

11.2 Monthly Sales Summary

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS revenue
FROM orders
GROUP BY month;

11.3 Events Occurring at a Specific Time

SELECT * FROM log_events
WHERE TIME(created_at) BETWEEN '09:00:00' AND '11:00:00';

MySQL offers a powerful suite of date and time data types to handle virtually any temporal data requirement. Each type—DATE, DATETIME, TIMESTAMP, TIME, and YEAR—has specific use cases depending on range, storage, and time zone handling. When working with dates and times, it is important to understand their behavior, especially with regard to time zones and automatic updates. Leveraging MySQL's rich set of functions for date manipulation allows for efficient querying and transformation of temporal data.