SQL Functions

Date and Time Functions

Master SQL date and time functions to extract parts, format dates, calculate intervals, and perform temporal analysis.

Extract & Transform — Pull year, month, day, and hour from any datetime column
Calculate Intervals — Add or subtract days, months, and years from dates Time-series analysis starts with mastering date functions.

Getting the Current Date and Time

DfCURRENT_DATE / NOW()

Returns the current date or timestamp from the database server. NOW() includes time, while CURRENT_DATE returns only the date portion.

-- Get current date and timestamp
SELECT
    CURRENT_DATE AS today,
    NOW() AS current_timestamp,
    CURRENT_TIMESTAMP AS also_now;

-- PostgreSQL-specific: extract with timezone
SELECT
    NOW() AS utc_now,
    NOW() AT TIME ZONE 'America/New_York' AS ny_time,
    NOW() AT TIME ZONE 'Asia/Tokyo' AS tokyo_time;

Use CURRENT_DATE instead of NOW() when you only need the date. It avoids time-zone ambiguity and makes comparisons cleaner.

Extracting Parts from Dates

DfEXTRACT()

Pulls a specific component (year, month, day, etc.) from a date or timestamp value. Returns an integer.

-- Extract year, month, and day from order dates
SELECT
    order_id,
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day,
    EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;

-- MySQL alternative using DATE_FORMAT
SELECT
    order_id,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day,
    DAYOFWEEK(order_date) AS day_of_week
FROM orders;

Common Date Parts Reference

Part	PostgreSQL	MySQL	SQL Server
Year	`EXTRACT(YEAR FROM date)`	`YEAR(date)`	`YEAR(date)`
Month	`EXTRACT(MONTH FROM date)`	`MONTH(date)`	`MONTH(date)`
Day	`EXTRACT(DAY FROM date)`	`DAY(date)`	`DAY(date)`
Day of Week	`EXTRACT(DOW FROM date)`	`DAYOFWEEK(date)`	`DATEPART(WEEKDAY, date)`
Day of Year	`EXTRACT(DOY FROM date)`	`DAYOFYEAR(date)`	`DATEPART(DAYOFYEAR, date)`

Formatting Dates

DfTO_CHAR / DATE_FORMAT

Converts a date or timestamp into a formatted string. The format pattern determines how the output appears.

-- PostgreSQL: format dates as readable strings
SELECT
    order_id,
    order_date,
    TO_CHAR(order_date, 'Month DD, YYYY') AS long_date,
    TO_CHAR(order_date, 'MM/DD/YYYY') AS us_format,
    TO_CHAR(order_date, 'DD-MM-YYYY HH24:MI') AS full_datetime
FROM orders;

-- MySQL: format dates using DATE_FORMAT
SELECT
    order_id,
    DATE_FORMAT(order_date, '%M %d, %Y') AS long_date,
    DATE_FORMAT(order_date, '%m/%d/%Y') AS us_format,
    DATE_FORMAT(order_date, '%d-%m-%Y %H:%i') AS full_datetime
FROM orders;

Format Patterns Cheat Sheet

Pattern	PostgreSQL	MySQL	Example Output
Full Month	`Month`	`%M`	January
Abbreviated Month	`Mon`	`%b`	Jan
Zero-padded Day	`DD`	`%d`	05
4-digit Year	`YYYY`	`%Y`	2026
24-hour Time	`HH24:MI`	`%H:%i`	14:30

Date Arithmetic

DfInterval / DATE_ADD / DATE_SUB

Perform arithmetic on dates by adding or subtracting time periods. Intervals define the amount of time to add or remove.

-- Add and subtract intervals from dates
SELECT
    CURRENT_DATE AS today,
    CURRENT_DATE + INTERVAL '7' DAY AS next_week,
    CURRENT_DATE - INTERVAL '30' DAY AS last_month,
    CURRENT_DATE + INTERVAL '3' MONTH AS three_months,
    CURRENT_DATE + INTERVAL '1' YEAR AS next_year;

-- MySQL equivalent using DATE_ADD and DATE_SUB
SELECT
    CURDATE() AS today,
    DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
    DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS last_month,
    DATE_ADD(CURDATE(), INTERVAL 3 MONTH) AS three_months;

-- Calculate days between two dates
SELECT
    order_id,
    order_date,
    CURRENT_DATE - order_date AS days_since_order,
    EXTRACT(DAY FROM AGE(CURRENT_DATE, order_date)) AS days_old
FROM orders;

Date Difference Functions

DfDATEDIFF / AGE()

Returns the difference between two dates as a number of days, months, or years. Useful for reporting time gaps and durations.

-- PostgreSQL: use AGE() for human-readable differences
SELECT
    employee_name,
    hire_date,
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed
FROM employees;

-- MySQL: use DATEDIFF for day counts
SELECT
    employee_name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) AS days_employed,
    DATEDIFF(CURDATE(), hire_date) / 365 AS approx_years
FROM employees;

Truncating Dates

DfDATE_TRUNC / TRUNCATE()

Rounds a date down to the nearest specified precision (day, week, month, quarter, year). Essential for grouping temporal data.

-- Group orders by month using DATE_TRUNC
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Group by quarter
SELECT
    DATE_TRUNC('quarter', order_date) AS quarter,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('quarter', order_date)
ORDER BY quarter;

Truncating dates changes the value used for grouping. Always verify your GROUP BY matches the truncation level to avoid incorrect aggregations.

Conditional Date Logic

-- Classify orders by recency
SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date >= CURRENT_DATE - INTERVAL '7' DAY THEN 'This Week'
        WHEN order_date >= CURRENT_DATE - INTERVAL '30' DAY THEN 'This Month'
        WHEN order_date >= CURRENT_DATE - INTERVAL '90' DAY THEN 'This Quarter'
        ELSE 'Older'
    END AS recency_bucket
FROM orders;

-- Find first and last orders per customer
SELECT
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    MAX(order_date) - MIN(order_date) AS customer_lifespan
FROM orders
GROUP BY customer_id;

Practical Examples

Monthly Revenue Report

-- Generate a monthly revenue report with growth calculation
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

Business Day Calculator

-- Count business days between two dates (excluding weekends)
SELECT
    order_id,
    order_date,
    ship_date,
    EXTRACT(DAY FROM AGE(ship_date, order_date))
        - (EXTRACT(DOW FROM ship_date) - EXTRACT(DOW FROM order_date)) / 7
        AS approx_business_days
FROM orders
WHERE ship_date IS NOT NULL;

Business day calculations vary by region. Consider holidays and local work calendars for production systems.

Summary

Key Takeaways

NOW() returns timestamp; CURRENT_DATE returns date only — choose based on precision needed.
EXTRACT() pulls individual parts from dates for grouping and filtering.
DATE_TRUNC() rounds dates down, making it ideal for temporal aggregation.
INTERVAL arithmetic is the most portable way to add or subtract time from dates.
Format functions (TO_CHAR, DATE_FORMAT) differ between databases — always check your dialect.
AGE() and DATEDIFF() handle date differences differently — PostgreSQL gives intervals, MySQL gives integers.

Date and Time Functions

Date and Time Functions

Getting the Current Date and Time

DfCURRENT_DATE / NOW()

Extracting Parts from Dates

DfEXTRACT()

Common Date Parts Reference

Formatting Dates

DfTO_CHAR / DATE_FORMAT

Format Patterns Cheat Sheet

Date Arithmetic

DfInterval / DATE_ADD / DATE_SUB

Date Difference Functions

DfDATEDIFF / AGE()

Truncating Dates

DfDATE_TRUNC / TRUNCATE()

Conditional Date Logic

Practical Examples

Monthly Revenue Report

Business Day Calculator

Summary

Key Takeaways

Premium Content

Need Expert SQL Help?