MySQL Dates and Times

MySQL Dates and Times: Types, Functions, and Safe Filtering

MySQL stores temporal values using dedicated DATE, TIME, DATETIME, and TIMESTAMP column types. Picking the right type and querying date ranges properly keeps timezone conversions consistent and lets B-tree indexes accelerate time-based reports.

Choosing Between DATE, DATETIME, and TIMESTAMP

Use DATE for birthdays or invoice dates, DATETIME for future scheduled appointments, and TIMESTAMP (or UTC DATETIME) for created_at audit logs. Storing application timestamps consistently in UTC prevents daylight saving time jumps from skewing order reports and SLA calculations.

Practical SQL Examples

This query inspects orders from demo_orders placed in the last 30 days and calculates delivery duration using DATEDIFF():

-- Fetch recent orders using an index-friendly range
SELECT
    id,
    customer_id,
    created_at,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_day,
    DATEDIFF(shipped_at, created_at) AS days_to_ship
FROM demo_orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC;

Common Mistakes and Best Practices

• Never Wrap Indexed Date Columns in Functions: Writing WHERE DATE(created_at) = '2026-09-12' forces a full table scan because MySQL must compute DATE() on every row. Always use a half-open range: WHERE created_at >= '2026-09-12 00:00:00' AND created_at < '2026-09-13 00:00:00'.
• Store Strings Only in ISO YYYY-MM-DD When Passing Literals: Always pass '2026-09-12', never ambiguous '09/12/2026'.

Frequently Asked Questions

What is the Year 2038 limit on TIMESTAMP in MySQL?

MySQL TIMESTAMP uses a 32-bit epoch integer in older storage formats (up to 2038-01-19); use DATETIME for birthdates, expirations, or long-term contracts.

What is the difference between NOW() and CURDATE()?

NOW() returns both date and time (YYYY-MM-DD HH:MM:SS), while CURDATE() returns only the date (YYYY-MM-DD).

How do I add 7 days to a date in MySQL?

Use DATE_ADD(created_at, INTERVAL 7 DAY) or created_at + INTERVAL 7 DAY.