Date and Time Functions

Date and time functions are essential for working with temporal data—calculating ages, finding time differences, formatting dates for display, extracting parts of dates, and performing date arithmetic. Whether you're building a report showing "orders placed in the last 30 days," calculating how many days until a subscription expires, formatting timestamps as "January 15, 2024," or finding all birthdays this month, date functions make it possible. MySQL provides comprehensive date/time functions covering everything from getting the current date to complex calendar calculations. This lesson covers the most important date and time functions with practical examples showing how to manipulate, format, and analyze temporal data in your queries.

📚 What You'll Learn

• How to get current date and time with NOW, CURDATE, CURTIME
• How to extract date parts with YEAR, MONTH, DAY, HOUR, etc.
• How to format dates with DATE_FORMAT
• How to add and subtract dates with DATE_ADD and DATE_SUB
• How to calculate differences with DATEDIFF and TIMESTAMPDIFF
• How to work with timestamps and conversions
• How to handle time zones
• How to find day of week, week of year, and quarters
• How to create dates from parts with MAKEDATE
• Practical patterns for date calculations

1. Getting Current Date and Time

NOW and CURRENT_TIMESTAMP

-- NOW() - current date and time
SELECT NOW();
-- Result: '2024-12-18 14:30:45'

-- CURRENT_TIMESTAMP - same as NOW()
SELECT CURRENT_TIMESTAMP;
-- Result: '2024-12-18 14:30:45'

-- Insert with current timestamp
INSERT INTO orders (customer_id, order_date, total)
VALUES (1001, NOW(), 299.99);

-- Update with current timestamp
UPDATE customers 
SET last_login = NOW() 
WHERE customer_id = 1001;

CURDATE and CURTIME

-- CURDATE() - current date only
SELECT CURDATE();
-- Result: '2024-12-18'

-- CURTIME() - current time only
SELECT CURTIME();
-- Result: '14:30:45'

-- Find orders placed today
SELECT * FROM orders
WHERE DATE(order_date) = CURDATE();

-- Compare dates (ignoring time)
SELECT * FROM customers
WHERE registration_date = CURDATE();

2. Extracting Parts of Dates

YEAR, MONTH, DAY

-- Extract year
SELECT YEAR('2024-12-18');
-- Result: 2024

-- Extract month (1-12)
SELECT MONTH('2024-12-18');
-- Result: 12

-- Extract day of month (1-31)
SELECT DAY('2024-12-18');
-- Result: 18

-- Group orders by year
SELECT 
    YEAR(order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(total) AS total_revenue
FROM orders
GROUP BY YEAR(order_date)
ORDER BY order_year;

HOUR, MINUTE, SECOND

-- Extract time components
SELECT HOUR('14:30:45');    -- Result: 14
SELECT MINUTE('14:30:45');  -- Result: 30
SELECT SECOND('14:30:45');  -- Result: 45

-- Analyze order patterns by hour
SELECT 
    HOUR(order_date) AS order_hour,
    COUNT(*) AS order_count
FROM orders
GROUP BY HOUR(order_date)
ORDER BY order_hour;

DAYOFWEEK, DAYNAME, MONTHNAME

-- DAYOFWEEK (1=Sunday, 2=Monday, ..., 7=Saturday)
SELECT DAYOFWEEK('2024-12-18');
-- Result: 4 (Wednesday)

-- DAYNAME - name of day
SELECT DAYNAME('2024-12-18');
-- Result: 'Wednesday'

-- MONTHNAME - name of month
SELECT MONTHNAME('2024-12-18');
-- Result: 'December'

-- Sales by day of week
SELECT 
    DAYNAME(order_date) AS day_name,
    COUNT(*) AS order_count,
    SUM(total) AS total_sales
FROM orders
GROUP BY DAYNAME(order_date), DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);

WEEK, QUARTER, DAYOFYEAR

-- WEEK - week of year (0-53)
SELECT WEEK('2024-12-18');
-- Result: 51

-- QUARTER - quarter of year (1-4)
SELECT QUARTER('2024-12-18');
-- Result: 4

-- DAYOFYEAR - day of year (1-366)
SELECT DAYOFYEAR('2024-12-18');
-- Result: 353

-- Quarterly sales report
SELECT 
    YEAR(order_date) AS year,
    QUARTER(order_date) AS quarter,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY year, quarter;

3. DATE_FORMAT: Custom Date Formatting

Common Format Patterns

-- DATE_FORMAT(date, format)

-- US format: MM/DD/YYYY
SELECT DATE_FORMAT('2024-12-18', '%m/%d/%Y');
-- Result: '12/18/2024'

-- Long format: Month DD, YYYY
SELECT DATE_FORMAT('2024-12-18', '%M %d, %Y');
-- Result: 'December 18, 2024'

-- Full date with day name
SELECT DATE_FORMAT('2024-12-18', '%W, %M %d, %Y');
-- Result: 'Wednesday, December 18, 2024'

-- Short date: Mon DD, YY
SELECT DATE_FORMAT('2024-12-18', '%b %d, %y');
-- Result: 'Dec 18, 24'

-- ISO 8601 format
SELECT DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%s');
-- Result: '2024-12-18T14:30:45'

Format Specifiers

Common DATE_FORMAT specifiers:

%Y - Year, 4 digits (2024)
%y - Year, 2 digits (24)
%M - Month name (December)
%m - Month, 2 digits (12)
%b - Abbreviated month (Dec)
%d - Day of month, 2 digits (18)
%D - Day with suffix (18th)
%W - Weekday name (Wednesday)
%w - Weekday number (0=Sunday, 6=Saturday)
%a - Abbreviated weekday (Wed)

%H - Hour, 24-hour (14)
%h - Hour, 12-hour (02)
%i - Minutes (30)
%s - Seconds (45)
%p - AM or PM

%c - Month number (12)
%e - Day of month (18)
%r - Time, 12-hour (02:30:45 PM)
%T - Time, 24-hour (14:30:45)

Practical Formatting Examples

-- Format orders for display
SELECT 
    order_id,
    customer_name,
    DATE_FORMAT(order_date, '%M %d, %Y at %h:%i %p') AS order_time,
    total
FROM orders;
-- Result: 'December 18, 2024 at 02:30 PM'

-- Month-year for grouping
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

4. Adding and Subtracting Dates

DATE_ADD and DATE_SUB

-- DATE_ADD(date, INTERVAL value unit)

-- Add days
SELECT DATE_ADD('2024-12-18', INTERVAL 7 DAY);
-- Result: '2024-12-25'

-- Add months
SELECT DATE_ADD('2024-12-18', INTERVAL 3 MONTH);
-- Result: '2025-03-18'

-- Add years
SELECT DATE_ADD('2024-12-18', INTERVAL 1 YEAR);
-- Result: '2025-12-18'

-- DATE_SUB(date, INTERVAL value unit)
SELECT DATE_SUB('2024-12-18', INTERVAL 30 DAY);
-- Result: '2024-11-18'

-- Find orders from last 30 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Calculate subscription expiration
SELECT 
    customer_id,
    subscription_start,
    DATE_ADD(subscription_start, INTERVAL 1 YEAR) AS expiration_date
FROM subscriptions;

Interval Units

Available INTERVAL units:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Complex Intervals

-- Add hours and minutes
SELECT DATE_ADD('2024-12-18 14:00:00', INTERVAL '2:30' HOUR_MINUTE);
-- Result: '2024-12-18 16:30:00'

-- Add days and hours
SELECT DATE_ADD('2024-12-18 14:00:00', INTERVAL '2 12' DAY_HOUR);
-- Result: '2024-12-21 02:00:00'

-- Add years and months
SELECT DATE_ADD('2024-12-18', INTERVAL '1-6' YEAR_MONTH);
-- Result: '2026-06-18'

5. Calculating Date Differences

DATEDIFF - Days Between Dates

-- DATEDIFF(date1, date2) - returns difference in days
SELECT DATEDIFF('2024-12-25', '2024-12-18');
-- Result: 7

SELECT DATEDIFF('2024-12-18', '2024-12-25');
-- Result: -7

-- Days since order
SELECT 
    order_id,
    order_date,
    DATEDIFF(CURDATE(), order_date) AS days_ago
FROM orders;

-- Find overdue invoices (30 days past due)
SELECT * FROM invoices
WHERE DATEDIFF(CURDATE(), invoice_date) > 30
    AND status = 'unpaid';

TIMESTAMPDIFF - Flexible Date Differences

-- TIMESTAMPDIFF(unit, start_date, end_date)

-- Difference in years
SELECT TIMESTAMPDIFF(YEAR, '2000-01-15', '2024-12-18');
-- Result: 24

-- Difference in months
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-12-18');
-- Result: 11

-- Difference in hours
SELECT TIMESTAMPDIFF(HOUR, '2024-12-18 10:00:00', '2024-12-18 14:30:00');
-- Result: 4

-- Calculate age
SELECT 
    customer_id,
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM customers;

-- Employee tenure in years
SELECT 
    employee_id,
    name,
    hire_date,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_employed,
    TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_employed
FROM employees;

6. Date Extraction and Conversion

DATE, TIME, and TIMESTAMP

-- DATE() - extract date part
SELECT DATE('2024-12-18 14:30:45');
-- Result: '2024-12-18'

-- TIME() - extract time part
SELECT TIME('2024-12-18 14:30:45');
-- Result: '14:30:45'

-- Compare dates ignoring time
SELECT * FROM orders
WHERE DATE(order_date) = '2024-12-18';

-- Orders placed today
SELECT * FROM orders
WHERE DATE(order_date) = CURDATE();

STR_TO_DATE - Parse String to Date

-- STR_TO_DATE(string, format)

-- Parse US format
SELECT STR_TO_DATE('12/18/2024', '%m/%d/%Y');
-- Result: '2024-12-18'

-- Parse with month name
SELECT STR_TO_DATE('December 18, 2024', '%M %d, %Y');
-- Result: '2024-12-18'

-- Parse datetime
SELECT STR_TO_DATE('2024-12-18 14:30:45', '%Y-%m-%d %H:%i:%s');
-- Result: '2024-12-18 14:30:45'

-- Convert text dates in import
UPDATE temp_orders
SET order_date = STR_TO_DATE(date_string, '%m/%d/%Y')
WHERE date_string IS NOT NULL;

7. Special Date Functions

LAST_DAY - Last Day of Month

-- LAST_DAY(date) - returns last day of month
SELECT LAST_DAY('2024-02-15');
-- Result: '2024-02-29' (leap year)

SELECT LAST_DAY('2024-12-18');
-- Result: '2024-12-31'

-- Find subscriptions expiring this month
SELECT * FROM subscriptions
WHERE LAST_DAY(expiration_date) = LAST_DAY(CURDATE());

-- Calculate days remaining in month
SELECT 
    DATEDIFF(LAST_DAY(CURDATE()), CURDATE()) AS days_left_in_month;

MAKEDATE - Create Date from Year and Day

-- MAKEDATE(year, day_of_year)
SELECT MAKEDATE(2024, 1);
-- Result: '2024-01-01'

SELECT MAKEDATE(2024, 365);
-- Result: '2024-12-30'

SELECT MAKEDATE(2024, 366);
-- Result: '2024-12-31' (leap year)

UNIX_TIMESTAMP and FROM_UNIXTIME

-- UNIX_TIMESTAMP() - convert to Unix timestamp
SELECT UNIX_TIMESTAMP('2024-12-18 14:30:45');
-- Result: 1734534645

SELECT UNIX_TIMESTAMP(NOW());
-- Current Unix timestamp

-- FROM_UNIXTIME() - convert from Unix timestamp
SELECT FROM_UNIXTIME(1734534645);
-- Result: '2024-12-18 14:30:45'

-- Store as timestamp, display as date
SELECT 
    event_id,
    FROM_UNIXTIME(event_timestamp) AS event_date
FROM events;

8. Practical Date Patterns

Age Calculation

-- Calculate precise age
SELECT 
    customer_id,
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age,
    CONCAT(
        TIMESTAMPDIFF(YEAR, birth_date, CURDATE()),
        ' years, ',
        TIMESTAMPDIFF(MONTH, birth_date, CURDATE()) % 12,
        ' months'
    ) AS age_detailed
FROM customers;

-- Find customers with birthdays this month
SELECT * FROM customers
WHERE MONTH(birth_date) = MONTH(CURDATE())
ORDER BY DAY(birth_date);

Business Days Calculation

-- Check if date is weekend
SELECT 
    order_date,
    CASE
        WHEN DAYOFWEEK(order_date) IN (1, 7) THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM orders;

-- Count weekdays between dates (approximate)
SELECT 
    order_date,
    ship_date,
    DATEDIFF(ship_date, order_date) AS total_days,
    -- Rough business days (excludes weekends)
    DATEDIFF(ship_date, order_date) 
        - (WEEK(ship_date) - WEEK(order_date)) * 2 AS business_days_approx
FROM orders;

Date Ranges and Periods

-- Orders this week
SELECT * FROM orders
WHERE YEARWEEK(order_date) = YEARWEEK(CURDATE());

-- Orders this month
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
    AND MONTH(order_date) = MONTH(CURDATE());

-- Orders this quarter
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
    AND QUARTER(order_date) = QUARTER(CURDATE());

-- Orders this year
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE());

-- Orders in last 7 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- Orders between dates
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

9. 🎯 Practice Exercises

customers (customer_id, name, registration_date, birth_date, last_login)
orders (order_id, customer_id, order_date, total)

-- Task 1: Calculate age
SELECT 
    customer_id,
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM customers
ORDER BY age DESC;


-- Task 2: Inactive customers (90+ days since last login)
SELECT 
    customer_id,
    name,
    last_login,
    DATEDIFF(CURDATE(), last_login) AS days_inactive
FROM customers
WHERE DATEDIFF(CURDATE(), last_login) >= 90
ORDER BY days_inactive DESC;


-- Task 3: Days since last order
SELECT 
    c.customer_id,
    c.name,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY days_since_order DESC;


-- Task 4: Birthdays this month
SELECT 
    customer_id,
    name,
    birth_date,
    DAY(birth_date) AS birthday_day,
    CONCAT(MONTHNAME(birth_date), ' ', DAY(birth_date)) AS birthday
FROM customers
WHERE MONTH(birth_date) = MONTH(CURDATE())
ORDER BY DAY(birth_date);

orders (order_id, customer_id, order_date, total, status)

-- Task 1: Monthly sales for current year
SELECT 
    MONTHNAME(order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
    AND status = 'completed'
GROUP BY MONTH(order_date), MONTHNAME(order_date)
ORDER BY MONTH(order_date);


-- Task 2: Quarterly comparison
SELECT 
    YEAR(order_date) AS year,
    CONCAT('Q', QUARTER(order_date)) AS quarter,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
    AND status = 'completed'
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY year, QUARTER(order_date);


-- Task 3: Day-of-week analysis
SELECT 
    DAYNAME(order_date) AS day_name,
    COUNT(*) AS order_count,
    SUM(total) AS total_revenue,
    AVG(total) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DAYNAME(order_date), DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);


-- Task 4: Year-over-year growth
SELECT 
    MONTH(order_date) AS month_num,
    MONTHNAME(order_date) AS month_name,
    SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) - 1 
             THEN total ELSE 0 END) AS last_year_revenue,
    SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) 
             THEN total ELSE 0 END) AS this_year_revenue,
    ROUND(
        (SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) THEN total ELSE 0 END) -
         SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) - 1 THEN total ELSE 0 END)) /
        SUM(CASE WHEN YEAR(order_date) = YEAR(CURDATE()) - 1 THEN total ELSE 0 END) * 100,
        2
    ) AS growth_percentage
FROM orders
WHERE YEAR(order_date) IN (YEAR(CURDATE()), YEAR(CURDATE()) - 1)
    AND status = 'completed'
GROUP BY MONTH(order_date), MONTHNAME(order_date)
ORDER BY month_num;

subscriptions (subscription_id, customer_id, start_date, end_date, plan_type, status)
customers (customer_id, name, email)

-- Comprehensive subscription dashboard
SELECT 
    s.subscription_id,
    c.name,
    c.email,
    s.plan_type,
    s.start_date,
    s.end_date,
    s.status,
    
    -- Subscription duration
    DATEDIFF(s.end_date, s.start_date) AS subscription_days,
    TIMESTAMPDIFF(MONTH, s.start_date, s.end_date) AS subscription_months,
    
    -- Days active/remaining
    CASE
        WHEN s.status = 'active' THEN DATEDIFF(CURDATE(), s.start_date)
        ELSE DATEDIFF(s.end_date, s.start_date)
    END AS days_active,
    
    CASE
        WHEN s.status = 'active' THEN DATEDIFF(s.end_date, CURDATE())
        ELSE 0
    END AS days_remaining,
    
    -- Renewal status
    CASE
        WHEN s.status = 'cancelled' THEN 'Cancelled'
        WHEN DATEDIFF(s.end_date, CURDATE()) < 0 THEN 'Expired'
        WHEN DATEDIFF(s.end_date, CURDATE()) <= 7 THEN 'Expires This Week'
        WHEN DATEDIFF(s.end_date, CURDATE()) <= 30 THEN 'Expires This Month'
        WHEN DATEDIFF(s.end_date, CURDATE()) <= 60 THEN 'Expires Soon'
        ELSE 'Active'
    END AS renewal_status,
    
    -- Next renewal date
    DATE_ADD(s.end_date, INTERVAL 1 YEAR) AS next_renewal_date,
    
    -- Formatted dates for display
    DATE_FORMAT(s.start_date, '%M %d, %Y') AS start_date_formatted,
    DATE_FORMAT(s.end_date, '%M %d, %Y') AS end_date_formatted,
    
    -- Subscription age
    CONCAT(
        TIMESTAMPDIFF(YEAR, s.start_date, CURDATE()), ' years, ',
        TIMESTAMPDIFF(MONTH, s.start_date, CURDATE()) % 12, ' months'
    ) AS subscription_age,
    
    -- Churn risk indicator
    CASE
        WHEN s.status = 'cancelled' THEN 'Already Churned'
        WHEN DATEDIFF(s.end_date, CURDATE()) <= 7 THEN 'High Risk'
        WHEN DATEDIFF(s.end_date, CURDATE()) <= 30 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS churn_risk

FROM subscriptions s
INNER JOIN customers c ON s.customer_id = c.customer_id
WHERE s.end_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
ORDER BY 
    CASE
        WHEN s.status = 'active' AND DATEDIFF(s.end_date, CURDATE()) <= 7 THEN 1
        WHEN s.status = 'active' AND DATEDIFF(s.end_date, CURDATE()) <= 30 THEN 2
        WHEN s.status = 'active' THEN 3
        ELSE 4
    END,
    days_remaining;


-- Summary statistics
SELECT 
    status,
    plan_type,
    COUNT(*) AS subscription_count,
    AVG(TIMESTAMPDIFF(MONTH, start_date, end_date)) AS avg_duration_months,
    SUM(CASE WHEN DATEDIFF(end_date, CURDATE()) BETWEEN 0 AND 30 
             THEN 1 ELSE 0 END) AS expiring_next_month
FROM subscriptions
GROUP BY status, plan_type
ORDER BY status, plan_type;

📝 Key Takeaways

• NOW() returns current date and time
• CURDATE() returns current date; CURTIME() returns current time
• YEAR, MONTH, DAY extract date parts
• HOUR, MINUTE, SECOND extract time parts
• DATE_FORMAT formats dates with custom patterns
• DATE_ADD/DATE_SUB perform date arithmetic with INTERVAL
• DATEDIFF calculates days between dates
• TIMESTAMPDIFF calculates differences in any unit
• DAYNAME, MONTHNAME return day/month names
• QUARTER, WEEK, DAYOFYEAR extract calendar positions
• STR_TO_DATE parses strings into dates
• LAST_DAY returns last day of month
• Date functions essential for temporal analysis and reporting
• Always use proper date comparisons (not string comparisons)
• Combine date functions for complex time-based queries