Date and Time Functions — Working with Temporal Data

---

title: “Date and Time Functions” part: 3 topic_number: 2 slug: “date-functions” difficulty: “Advanced” prerequisites: “aggregate-functions” —

Date and Time Functions — Working with Temporal Data

What Is It?

MySQL provides powerful functions for working with dates and times: extracting parts (year, month, day), calculating differences, adding/subtracting intervals, and formatting output.

Real-world analogy: Like a sophisticated calendar app that can tell you “2 weeks from today”, “days until deadline”, or “all events in Q3 2023”.

When you’d use it:

• Calculate ages, tenures, or durations
• Filter by date ranges
• Extract parts (year, month, day of week)
• Format dates for reports
• Time-based analytics (monthly trends, etc.)

---

Common Functions Overview

Function	Purpose	Example
NOW()	Current datetime	2026-04-30 14:30:00
CURDATE()	Current date	2026-04-30
YEAR(date)	Extract year	2026
MONTH(date)	Extract month	4
DAY(date)	Extract day	30
DAYOFWEEK(date)	Day of week (1=Sun)	4 (Wed)
DATE_ADD(date, INTERVAL n unit)	Add time	2026-05-30
DATE_SUB(date, INTERVAL n unit)	Subtract time	2026-03-30
DATEDIFF(date1, date2)	Days between	365
DATE_FORMAT(date, format)	Format output	Apr 30, 2026

---

Basic Examples

Extract Date Parts

SELECT 
    first_name,
    hire_date,
    YEAR(hire_date) AS hire_year,
    MONTH(hire_date) AS hire_month,
    DAY(hire_date) AS hire_day,
    DAYNAME(hire_date) AS hire_day_name
FROM employees
ORDER BY hire_date DESC
LIMIT 5;

Expected output:

first_name	hire_date	hire_year	hire_month	hire_day	hire_day_name
Grace	2023-09-15	2023	9	15	Friday
Olivia	2023-08-20	2023	8	20	Sunday
Mia	2023-06-10	2023	6	10	Saturday
…	…	…	…	…	…

Date components extracted.

Calculate Tenure

SELECT 
    first_name,
    last_name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) AS days_employed,
    ROUND(DATEDIFF(CURDATE(), hire_date) / 365.25, 1) AS years_employed
FROM employees
ORDER BY days_employed DESC
LIMIT 5;

Expected output:

first_name	last_name	hire_date	days_employed	years_employed
George	Jones	2017-07-12	3214	8.8
Ivy	Anderson	2018-02-20	2991	8.2
Frank	Miller	2018-09-01	2798	7.7
…	…	…	…	…

Longest-tenured employees.

---

Going Deeper

Date Arithmetic

SELECT 
    first_name,
    hire_date,
    DATE_ADD(hire_date, INTERVAL 90 DAY) AS probation_end,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS first_anniversary,
    DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AS six_months_ago
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
ORDER BY hire_date DESC;

Expected output:

first_name	hire_date	probation_end	first_anniversary	six_months_ago
Grace	2023-09-15	2023-12-14	2024-09-15	2025-10-30
Olivia	2023-08-20	2023-11-18	2024-08-20	2025-10-30
…	…	…	…	…

Recent hires with calculated dates.

Format Dates for Reports

SELECT 
    first_name,
    DATE_FORMAT(hire_date, '%M %d, %Y') AS formatted_hire_date,
    DATE_FORMAT(hire_date, '%m/%d/%y') AS short_date,
    DATE_FORMAT(hire_date, '%W') AS day_of_week,
    DATE_FORMAT(hire_date, '%Y-Q%q') AS hire_quarter
FROM employees
ORDER BY hire_date DESC
LIMIT 5;

Expected output:

first_name	formatted_hire_date	short_date	day_of_week	hire_quarter
Grace	September 15, 2023	09/15/23	Friday	2023-Q3
Olivia	August 20, 2023	08/20/23	Sunday	2023-Q3
Mia	June 10, 2023	06/10/23	Saturday	2023-Q2

Professional date formatting.

Common format specifiers:

• %Y = 4-digit year, %y = 2-digit year
• %M = full month name, %m = 2-digit month
• %d = day of month, %W = weekday name
• %H:%i:%s = time (24-hour:minute:second)

Group by Time Periods

SELECT 
    YEAR(hire_date) AS hire_year,
    QUARTER(hire_date) AS hire_quarter,
    COUNT(*) AS new_hires,
    AVG(salary) AS avg_starting_salary
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY hire_year DESC, hire_quarter DESC;

Expected output:

hire_year	hire_quarter	new_hires	avg_starting_salary
2023	3	2	61000.00
2023	2	1	57000.00
2022	1	1	69000.00
2021	4	1	66000.00
…	…	…	…

Hiring trends by quarter.

Age Calculations

-- Add date_of_birth column for demo
ALTER TABLE employees ADD COLUMN date_of_birth DATE;
UPDATE employees SET date_of_birth = DATE_SUB(hire_date, INTERVAL 25 YEAR) WHERE employee_id <= 10;

SELECT 
    first_name,
    last_name,
    date_of_birth,
    TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 30 THEN 'Under 30'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 40 THEN '30-39'
        WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 50 THEN '40-49'
        ELSE '50+'
    END AS age_group
FROM employees
WHERE date_of_birth IS NOT NULL
ORDER BY age DESC;

Expected output:

first_name	last_name	date_of_birth	age	age_group
George	Jones	1992-07-12	33	30-39
Ivy	Anderson	1993-02-20	33	30-39
Frank	Miller	1993-09-01	32	30-39
…	…	…	…	…

Age demographics.

Pause and Predict: What’s the difference between DATEDIFF and TIMESTAMPDIFF?

Answer

**DATEDIFF** returns **days** between two dates (always whole days): ```sql DATEDIFF('2026-05-01', '2026-04-01') → 30 ``` **TIMESTAMPDIFF** returns difference in specified unit (second, minute, hour, day, month, year): ```sql TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-05-01') → 4 TIMESTAMPDIFF(YEAR, '2020-01-01', '2026-04-30') → 6 ``` Use TIMESTAMPDIFF for flexible unit control, DATEDIFF for simple day counts.

---

Watch Out — Common Mistakes

Mistake #1: Forgetting Time Zones

--  NOW() uses server timezone
SELECT NOW();  -- Might not match your local time!

Fix: Use CONVERT_TZ or set session timezone:

-- • Convert to specific timezone
SELECT CONVERT_TZ(NOW(), 'UTC', 'America/New_York');

-- Or set session
SET time_zone = 'America/New_York';
SELECT NOW();

Mistake #2: String Comparison Instead of Date Comparison

--  WRONG (string comparison)
SELECT * FROM employees WHERE hire_date > '2020-1-1';  -- Missing leading zeros!

Better:

-- • CORRECT (proper date format)
SELECT * FROM employees WHERE hire_date > '2020-01-01';

-- Or use DATE function
SELECT * FROM employees WHERE hire_date > DATE('2020-01-01');

Mistake #3: Incorrect Interval Units

--  WRONG
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTHS);  -- 'MONTHS' is plural!

Fix: Use singular units:

-- • CORRECT
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);

Valid units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

---

Edge Case Spotlight

Leap Years

SELECT 
    DATE_ADD('2024-02-28', INTERVAL 1 DAY) AS next_day,  -- 2024-02-29 (leap year!)
    DATE_ADD('2025-02-28', INTERVAL 1 DAY) AS next_day_2025;  -- 2025-03-01

MySQL handles leap years automatically.

Last Day of Month

SELECT 
    LAST_DAY('2026-02-15') AS last_day_feb,  -- 2026-02-28
    LAST_DAY('2026-04-01') AS last_day_apr;  -- 2026-04-30

Useful for “end of month” reports.

---

Try This

Exercise 1 (Guided)

Find all employees hired in the last 2 years. Show first_name, last_name, hire_date, and days_since_hire. Sort by most recent.

Hint

WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR), use DATEDIFF for days.

Exercise 2 (Independent)

Create an “anniversary report”: find employees whose hire date anniversary is in the next 30 days. Show name, hire_date, anniversary_date (this year), and years_with_company.

Hint

Use DATE_FORMAT to extract month/day, compare to current date, DATE_ADD for anniversary, TIMESTAMPDIFF for years.

Exercise 3 (Challenge)

Create a hiring trend analysis by month for the past 3 years. Show year, month_name, hire_count, and cumulative_hires_ytd (year-to-date cumulative). Sort by year DESC, month.

Hint

Filter last 3 years, GROUP BY YEAR and MONTH, use SUM() OVER (PARTITION BY year ORDER BY month) for cumulative.

---

Answer Key

Exercise 1 Answer

```sql SELECT first_name, last_name, hire_date, DATEDIFF(CURDATE(), hire_date) AS days_since_hire FROM employees WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR) ORDER BY hire_date DESC; ``` **Expected output:** | first_name | last_name | hire_date | days_since_hire | |------------|-----------|-----------|-----------------| | Grace | 2023-09-15 | 957 | | Olivia | 2023-08-20 | 983 | | Mia | 2023-06-10 | 1054 | Recent hires within last 2 years.

Exercise 2 Answer

```sql SELECT first_name, last_name, hire_date, DATE_ADD( hire_date, INTERVAL YEAR(CURDATE()) - YEAR(hire_date) YEAR ) AS anniversary_date, TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_with_company FROM employees WHERE DATE_ADD( hire_date, INTERVAL YEAR(CURDATE()) - YEAR(hire_date) YEAR ) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) ORDER BY anniversary_date; ``` **Expected output:** | first_name | last_name | hire_date | anniversary_date | years_with_company | |------------|-----------|-----------|------------------|-------------------| | Eve | Davis | 2022-05-14 | 2026-05-14 | 4 | | Bob | Smith | 2020-05-10 | 2026-05-10 | 6 | Upcoming anniversaries in next 30 days.

Exercise 3 Answer

```sql SELECT YEAR(hire_date) AS year, MONTHNAME(hire_date) AS month_name, COUNT(*) AS hire_count, SUM(COUNT(*)) OVER ( PARTITION BY YEAR(hire_date) ORDER BY MONTH(hire_date) ) AS cumulative_hires_ytd FROM employees WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR) GROUP BY YEAR(hire_date), MONTH(hire_date), MONTHNAME(hire_date) ORDER BY year DESC, MONTH(hire_date); ``` **Expected output:** | year | month_name | hire_count | cumulative_hires_ytd | |------|------------|------------|----------------------| | 2023 | June | 1 | 1 | | 2023 | August | 1 | 2 | | 2023 | September | 1 | 3 | Hiring trends with year-to-date cumulative counts. **Note:** Uses window function (SUM OVER) for cumulative total.

---

Quick Recap

• CURDATE() / NOW() get current date/time
• YEAR(), MONTH(), DAY() extract parts
• DATE_ADD() / DATE_SUB() perform date arithmetic
• DATEDIFF() counts days between dates
• TIMESTAMPDIFF() difference in any unit
• DATE_FORMAT() formats output
• Use proper date formats ('YYYY-MM-DD')
• Watch for timezone issues with NOW()

---

Up Next

Next topic: Views (Virtual Tables) → part3_03_views.md

Type ‘next’ when ready to continue!