MySQL Date & Time Functions Interview Questions

DATE_ADD/DATE_SUB adjust dates by intervals. INTERVAL specifies unit (DAY, MONTH, YEAR, HOUR, etc.). Essential for relative date calculations.

-- Date arithmetic
SELECT DATE_ADD('2024-01-15', INTERVAL 10 DAY);  -- 2024-01-25
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- One month ago
SELECT DATE_ADD(hired_date, INTERVAL 3 YEAR) FROM employees;

-- Interval units: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND
SELECT * FROM invoices WHERE due_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE();

-- Complex intervals
SELECT DATE_ADD('2024-01-15', INTERVAL '2-3' YEAR_MONTH);  -- Add 2 years 3 months
SELECT DATE_ADD(NOW(), INTERVAL '1-05:30:00' DAY_SECOND);

Why it matters: Calculate dates relative to current date.

Real applications: Subscriptions, renewals, deadlines, reports.

Common mistakes: Missing INTERVAL keyword, wrong interval units.

DATEDIFF returns days between dates. TIMESTAMPDIFF returns difference in specified units. TIMEDIFF returns time difference.

-- Difference calculations
SELECT DATEDIFF('2024-12-25', '2024-01-01');  -- 359 days
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-12-31');  -- 11
SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) FROM events;
SELECT TIMEDIFF('15:30:00', '10:00:00');  -- '05:30:00'

-- Use cases
SELECT customer_id, DATEDIFF(CURDATE(), last_order_date) as days_since_order
FROM customers WHERE DATEDIFF(CURDATE(), last_order_date) > 90;

SELECT name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age
FROM employees;

Why it matters: Calculate time differences for reports and analysis.

Real applications: Age calculation, project duration, customer dormancy.

Common mistakes: Order of parameters (from, to), DATEDIFF returns only days.

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND extract individual components. Useful for grouping and filtering by specific time periods.

-- Extract components
SELECT YEAR('2024-04-05');  -- 2024
SELECT MONTH('2024-04-05');  -- 4
SELECT DAY('2024-04-05');  -- 5
SELECT HOUR('14:30:45');  -- 14
SELECT MINUTE('14:30:45');  -- 30

-- Group by time periods
SELECT YEAR(order_date) as year, SUM(amount) FROM orders GROUP BY YEAR(order_date);
SELECT MONTH(order_date) as month, COUNT(*) FROM orders WHERE YEAR(order_date) = 2024 GROUP BY MONTH(order_date);

-- Filter by parts
SELECT * FROM events WHERE MONTH(event_date) = 12 AND DAY(event_date) = 25;  -- Christmas

Why it matters: Extract and group by time components.

Real applications: Seasonal reports, birthday tracking, hourly analytics.

Common mistakes: Preventing index usage with functions, not considering timezone.

DATE_FORMAT formats dates for display. STR_TO_DATE parses strings into dates. Essential for input/output handling.

-- Format for output
SELECT DATE_FORMAT('2024-04-05', '%Y-%m-%d');  -- '2024-04-05'
SELECT DATE_FORMAT('2024-04-05', '%M %d, %Y');  -- 'April 05, 2024'
SELECT TIME_FORMAT('14:30:45', '%h:%i %p');  -- '02:30 PM'

-- Parse string input
SELECT STR_TO_DATE('04/05/2024', '%m/%d/%Y');  -- '2024-04-05'
SELECT STR_TO_DATE('2024-04-05 14:30:45', '%Y-%m-%d %H:%i:%S');

-- Use in application
SELECT DATE_FORMAT(created_at, '%M %Y') as month_year FROM orders;
INSERT INTO events (event_date) VALUES (STR_TO_DATE('04/05/2024', '%m/%d/%Y'));

Why it matters: Format dates for users, parse user input.

Real applications: Localized date display, user input handling.

Common mistakes: Format string errors, timezone handling.

WEEK returns week number. QUARTER returns fiscal quarter. LAST_DAY returns last day of month. Useful for period-based grouping.

-- Period functions
SELECT WEEK('2024-04-05');  -- 15 (week number)
SELECT QUARTER('2024-04-05');  -- 2 (Q2)
SELECT LAST_DAY('2024-02-05');  -- '2024-02-29' (leap year)

-- Group by periods
SELECT WEEK(order_date) as week, SUM(amount) FROM orders 
WHERE YEAR(order_date) = 2024 GROUP BY WEEK(order_date);

SELECT QUARTER(order_date) as quarter, COUNT(*) FROM orders GROUP BY QUARTER(order_date);

-- End of month billing
SELECT * FROM subscriptions WHERE next_billing_date = LAST_DAY(next_billing_date);

Why it matters: Weekly, quarterly, monthly reporting.

Real applications: Weekly/quarterly reports, billing cycles.

Common mistakes: Week starting day depends on mode, LAST_DAY with timezones.

DAYNAME returns day name. MONTHNAME returns month name. DAYOFWEEK returns day number (1=Sunday). Useful for human-readable output.

-- Named components
SELECT DAYNAME('2024-04-05');  -- 'Friday'
SELECT MONTHNAME('2024-04-05');  -- 'April'
SELECT DAYOFWEEK('2024-04-05');  -- 6 (Friday, 1=Sunday)

-- Filter by day
SELECT * FROM events WHERE DAYNAME(event_date) = 'Monday';
SELECT * FROM meetings WHERE DAYOFWEEK(meeting_date) NOT IN (1, 7);  -- Exclude weekends

-- Display friendly format
SELECT DATE_FORMAT(order_date, '%W, %M %d, %Y') FROM orders;

Why it matters: Human-readable date output for reports.

Real applications: Schedules, event displays, user-facing reports.

Common mistakes: Locale-specific output, dayofweek numbering (MySQL: 1=Sunday).

Best practices include: store UTC timestamps, use appropriate functions for calculations, avoid functions in WHERE/JOIN for performance, handle timezones explicitly.

-- Store UTC, display local
ALTER TABLE orders ADD COLUMN created_utc DATETIME DEFAULT UTC_TIMESTAMP();

-- Convert for display
SELECT CONVERT_TZ(created_utc, '+00:00', '-05:00') as created_eastern FROM orders;

-- Don't use functions in WHERE (prevents index use)
SELECT * FROM orders WHERE YEAR(order_date) = 2024;  -- Slow

-- Better: Use range
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- Precompute periods
ALTER TABLE orders ADD COLUMN year_month VARCHAR(7);
UPDATE orders SET year_month = DATE_FORMAT(order_date, '%Y-%m');
CREATE INDEX idx_year_month ON orders(year_month);

Why it matters: Performance and correctness in temporal queries.

Real applications: Multi-timezone systems, timezone-safe queries.

Common mistakes: Not handling timezones, functions preventing indexes.