MySQL Dates

Date Data Types

-- Format: YYYY-MM-DD
'2024-12-25'

-- Format: HH:MM:SS
'14:30:00'

-- Format: YYYY-MM-DD HH:MM:SS
'2024-12-25 14:30:00'

created_at TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP

🔹 Inserting Date Values

Insert dates using the proper format for each data type. MySQL accepts dates in 'YYYY-MM-DD' format, times in 'HH:MM:SS' format, and datetime in 'YYYY-MM-DD HH:MM:SS' format. You can also use functions like NOW() and CURDATE() for current values.

-- Insert specific dates
INSERT INTO events (event_date, event_time, created_at) 
VALUES ('2024-12-25', '14:30:00', '2024-12-25 14:30:00');

-- Insert current date/time
INSERT INTO events (event_date, event_time, created_at) 
VALUES (CURDATE(), CURTIME(), NOW());

-- Insert with date functions
INSERT INTO events (event_date, created_at) 
VALUES (DATE('2024-12-25'), CURRENT_TIMESTAMP);

🔹 Date Functions

MySQL provides many built-in functions for working with dates. These functions help you get current dates, extract parts of dates, format dates, calculate date differences, and perform date arithmetic. They are essential for date manipulation and reporting.

-- Current date and time functions
SELECT NOW();              -- Current datetime
SELECT CURDATE();          -- Current date
SELECT CURTIME();          -- Current time

-- Extract date parts
SELECT YEAR('2024-12-25');     -- Returns: 2024
SELECT MONTH('2024-12-25');    -- Returns: 12
SELECT DAY('2024-12-25');      -- Returns: 25

-- Date formatting
SELECT DATE_FORMAT(NOW(), '%W %M %Y');  -- Wednesday December 2024

🔹 Date Calculations

Perform calculations with dates using DATE_ADD, DATE_SUB, and DATEDIFF functions. Add or subtract intervals like days, months, or years. Calculate the difference between two dates. These operations are useful for scheduling, age calculations, and deadline tracking.

-- Add days to a date
SELECT DATE_ADD('2024-12-25', INTERVAL 7 DAY);  -- 2025-01-01

-- Subtract months from a date
SELECT DATE_SUB('2024-12-25', INTERVAL 2 MONTH);  -- 2024-10-25

-- Calculate difference between dates
SELECT DATEDIFF('2024-12-25', '2024-12-01');  -- 24 days

-- Add multiple intervals
SELECT DATE_ADD('2024-12-25', INTERVAL '1-2' YEAR_MONTH);  -- 2026-02-25

🔹 Querying with Dates

Filter records using date comparisons in WHERE clauses. Compare dates using standard operators like =, <, >, BETWEEN. Extract specific date parts for grouping and filtering. Use date functions to find records within specific time ranges or periods.

-- Find events on specific date
SELECT * FROM events 
WHERE event_date = '2024-12-25';

-- Find events in date range
SELECT * FROM events 
WHERE event_date BETWEEN '2024-12-01' AND '2024-12-31';

-- Find events in current month
SELECT * FROM events 
WHERE MONTH(event_date) = MONTH(CURDATE())
AND YEAR(event_date) = YEAR(CURDATE());

-- Find events in last 7 days
SELECT * FROM events 
WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

🔹 Automatic Timestamps

Use TIMESTAMP columns with DEFAULT and ON UPDATE clauses to automatically track record creation and modification times. This eliminates manual timestamp management and ensures accurate audit trails. TIMESTAMP columns automatically adjust for timezone differences, making them ideal for distributed applications.

-- Table with automatic timestamps
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP
);

-- Insert record (timestamps auto-filled)
INSERT INTO posts (title) VALUES ('My First Post');