🚀 SQL Roadmap 2026 — Part 15

📅 SQL Date & Time Functions — Working with Dates, Time & Time-Based Analytics

Almost every real-world analytics project involves dates.

Examples:

• When was the order placed?

• How many days did delivery take?

• What was the revenue in January?

• Which month had the highest sales?

• How many customers purchased this year?

• How long has an account been inactive?

• What is the month-over-month growth?

• Which transactions breached the SLA?

SQL provides powerful date and time functions to answer these questions.

🧠 1. Common Date & Time Data Types

Different databases support different date/time types, but commonly you'll encounter:

• DATE

• TIME

• TIMESTAMP

• DATETIME

DATE stores only the date: 2026-09-18

TIME stores only time: 14:30:25

TIMESTAMP stores both date and time: 2026-09-18 14:30:25

The exact data types and behavior vary by database.

📅 2. Current Date

Many databases provide a function for the current date.

For example:

SELECT CURRENT_DATE;

Result: 2026-09-18

The exact function can vary by SQL dialect.

⏰ 3. Current Date and Time

A common SQL expression is:

SELECT CURRENT_TIMESTAMP;

It returns the current date and time.

For example: 2026-09-18 21:39:00

Again, exact formatting depends on the database.

🔍 4. Extracting Year, Month & Day

Suppose: order_date = 2026-09-18

We may want: Year → 2026, Month → 9, Day → 18

A common SQL approach is:

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

Some databases use functions such as YEAR(order_date), MONTH(order_date), DAY(order_date).

So always check the syntax for your SQL dialect.

📊 5. Revenue by Year

Suppose we want annual revenue.

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY order_year;

Result:

• 2024 | 850000

• 2025 | 1120000

• 2026 | 1380000

This lets us analyze year-over-year business performance.

📆 6. Revenue by Month

We can group transactions by month.

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY
    year,
    month;

Important: Don't group only by month number if your data spans multiple years.

For example January 2025 and January 2026 both have month = 1. Grouping only by month could incorrectly combine them.

🗓️ 7. Month-Level Grouping

Some databases provide functions that truncate a date to the beginning of a month.

For example, PostgreSQL:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

This produces values such as 2026-01-01, 2026-02-01 representing each month. This is often convenient for time-series analysis.

📈 8. Month-over-Month Analysis

Suppose we have monthly revenue: Jan 100000, Feb 120000, Mar 110000. We can use LAG():

WITH monthly_sales 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 previous_month_revenue
FROM monthly_sales;

📊 9. Month-over-Month Growth %

We can extend the previous query: