Pathubs SQL Curriculum • Module 10

SQL Date & Time Functions Mastery

Master temporal data analysis in SQL: understand DATE vs TIMESTAMP, extract calendar parts, calculate date differences, execute safe half-open range queries, bucket time-series with DATE_TRUNC, and handle NULL dates.

⏱️ Estimated Time:35 Minutes

🎯 Level:Analytical SQL Fundamentals

📊 Track:Data Analytics & Backend Engineering

✨ Mode:Interactive Date Lab

Introduction: Real-World Time-Series Data in SQL

Almost every business entity in a database has a temporal timestamp: an order is placed on a specific date, a subscription renews monthly, or a user logs in at a given hour. Consider this orders table:

id	customer	order_date	shipped_at	amount
1	Rahul	2026-01-15 10:30:00	2026-01-18 16:00:00	$500
2	Priya	2026-01-31 18:20:00	2026-02-04 09:30:00	$850
3	Amit	2026-02-03 09:15:00	2026-02-05 11:45:00	$300
4	Neha	2026-02-14 14:45:00	NULL (Pending)	$620

To answer core business questions—such as “What were our total sales in January?”, “What is our average shipping lead time in days?”, or “Which hour of the day receives the most traffic?”—you must master SQL Date and Time functions.

DATE, TIME, and TIMESTAMP Data Types

Relational databases distinguish between three primary temporal data types:

Data Type	Stored Information	Standard Format	Example Value
DATE	Year, Month, Day only	`YYYY-MM-DD`	`2026-08-27`
TIME	Hour, Minute, Second, Microseconds	`HH:MI:SS`	`14:30:25`
TIMESTAMP	Both Calendar Date + Exact Time	`YYYY-MM-DD HH:MI:SS`	`2026-08-27 14:30:25`

Diagram 1: Visual Comparison of Temporal Types

DATE

2026-08-27

Calendar Day Only

TIME

14:30:25

Time-of-Day Only

TIMESTAMP

2026-08-27 14:30:25

Full Date + Clock Time

CURRENT_DATE & CURRENT_TIMESTAMP (NOW)

SQL provides dynamic system functions to capture the database server's current clock:

SELECT
CURRENT_DATE AS today_date,
CURRENT_TIMESTAMP AS exact_server_now;
-- PostgreSQL/MySQL also support: NOW()

Extracting Date Parts with EXTRACT()

Standard ANSI SQL provides EXTRACT(field FROM source) to retrieve individual numeric components from a date or timestamp:

SELECT
    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(HOUR FROM order_date) AS order_hour
FROM orders;

Diagram 2: Anatomy of EXTRACT() Decomposition

"2026-08-27 14:30:25"
YEAR
2026
MONTH
8
DAY
27
HOUR
14

EXTRACT in Real Business Analysis

Combining EXTRACT() with GROUP BY allows analysts to aggregate metrics across calendar cycles (e.g. seasonality trends):

SELECT
    EXTRACT(MONTH FROM order_date) AS order_month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY order_month ASC;

Date Comparisons (>, <, >=, <=, =)

SQL supports chronological comparisons using standard mathematical inequality operators:

-- Find orders placed on or after February 1st, 2026
SELECT *
FROM orders
WHERE order_date >= '2026-02-01';

Date Ranges & The Dangerous Midnight Cutoff Trap

Suppose you want all orders from January 2026. A common beginner mistake is writing:

-- ❌ DANGEROUS TRAP: Cuts off records on Jan 31st after 00:00:00!
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

Why BETWEEN Drops Afternoon Records

In SQL, '2026-01-31' is cast to '2026-01-31 00:00:00' (midnight). An order placed on January 31st at 18:20:00 is strictly greater than midnight, so it is silently excluded!

The professional, bulletproof pattern is the Half-Open Interval (>= start AND < next_period_start):

-- ✅ BULLETPROOF: Captures 100% of January transactions up to 23:59:59.999
WHERE order_date >= '2026-01-01'
AND order_date < '2026-02-01';

Diagram 3: Safe Half-Open Date Range vs BETWEEN Cutoff

BETWEEN Jan 1 AND Jan 31

Bound: Jan 31 00:00:00
❌ Drops Jan 31 @ 18:20

>= Jan 1 AND < Feb 1

Bound: Up to Feb 1 00:00:00
✅ Captures all Jan 31 transactions

Date Arithmetic With INTERVALs

You can add or subtract calendar duration units using the INTERVAL keyword:

SELECT
    order_date,
    order_date + INTERVAL '7 days' AS expected_delivery,
    order_date - INTERVAL '30 days' AS prior_month_date
FROM orders;

Calculating Date Differences (Fulfillment Lead Times)

Subtracting two timestamps calculates the exact duration between events:

-- PostgreSQL / ANSI: Difference between two timestamps
SELECT
    customer,
    order_date,
    shipped_at,
    shipped_at - order_date AS fulfillment_duration
FROM orders
WHERE shipped_at IS NOT NULL;

DATE_TRUNC(): Grouping by Time Periods

DATE_TRUNC('unit', timestamp) is the gold standard for time-series aggregation. It acts as a mathematical floor function, truncating timestamps to the beginning of the specified period (e.g. day, week, month, year):

SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY sales_month ASC;

Diagram 4: DATE_TRUNC('month') Time Bucketing

Original Timestamp
2026-02-14 14:45:00

── DATE_TRUNC('month') ──▶

Month Bucket Beginning
2026-02-01 00:00:00

Date Formatting (TO_CHAR / DATE_FORMAT)

Formatting converts a binary date/timestamp into a readable display string:

-- PostgreSQL: TO_CHAR(timestamp, format)
SELECT TO_CHAR(order_date, 'DD Mon YYYY, HH12:MI AM') AS formatted_date
FROM orders;
-- Yields: '15 Jan 2026, 10:30 AM'

Date & Time in WHERE Filtering

Combining temporal extraction and comparisons in your WHERE clause:

-- Filter orders from Q1 2026 with amounts over $500
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2026-04-01'
AND amount >= 500;

Date & Time With GROUP BY Aggregations

Calculating annual or monthly performance breakdowns:

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(amount) AS annual_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

Handling NULL Dates & Pending Milestones

In delivery tracking, orders that have not yet dispatched have shipped_at = NULL:

-- Find backlog orders that are still pending shipment
SELECT id, customer, order_date
FROM orders
WHERE shipped_at IS NULL;

Common SQL Date & Time Mistakes

1. The BETWEEN Midnight Cutoff

Using BETWEEN '2026-01-01' AND '2026-01-31', dropping timestamps after 00:00:00 on the final day.

2. Merging Multi-Year Months

Grouping strictly by EXTRACT(MONTH), blending January 2025 and January 2026 into one bucket.

3. Treating Dates as Plain Strings

Attempting string substring hacks instead of standard date arithmetic and extraction functions.

4. Confusing Display Formatting with Values

Formatting dates in subqueries and attempting chronological comparisons on text formatted strings.

Practical Analytical Query Exercises

Exercise 1: Monthly Sales Aggregation with DATE_TRUNC

SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(*) AS order_volume,
    SUM(amount) AS gross_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month ASC;

Exercise 2: Finding Slow Deliveries (Lead Time > 3 Days)

SELECT
id, customer, order_date, shipped_at,
shipped_at - order_date AS lead_time
FROM orders
WHERE shipped_at > order_date + INTERVAL '3 days';

⚡Live Interactive SQL Date & Time Practice Lab

Experiment with dynamic date extraction, half-open range queries, lead time calculations, and time bucketing

Extract Field:

id	Customer	Full Timestamp (order_date)	EXTRACT(MONTH)
1	Rahul Sharma	2026-01-15 10:30:00	1
2	Priya Patel	2026-01-31 18:20:00	1
3	Amit Verma	2026-02-03 09:15:00	2
4	Neha Singh	2026-02-14 14:45:00	2
5	Rohan Gupta	2026-02-20 11:10:00	2
6	Vikram Joshi	2026-03-02 08:00:00	3

SQL Date & Time Best Practices

Always Use Half-Open Intervals for Date Ranges: Use >= start AND < next_start instead of BETWEEN on timestamps.
Use DATE_TRUNC for Multi-Year Time Series: Avoid raw EXTRACT(MONTH) which collapses years into months 1-12.
Store Timestamps in UTC: Handle time zones at the presentation layer while keeping raw database storage standardized.
Check for NULL in Date Calculations: Use COALESCE() or filter with IS NOT NULL before computing durations.

What You Should Know Now: Checklist

✓Data Types: DATE (day only), TIME (clock only), TIMESTAMP (combined).
✓System Time: CURRENT_DATE and CURRENT_TIMESTAMP (NOW).
✓EXTRACT: Retrieves numeric components (YEAR, MONTH, DAY, HOUR).
✓Half-Open Intervals: Avoids the midnight cutoff trap when filtering timestamps.
✓DATE_TRUNC: Floors timestamps to period beginnings for clean time-series grouping.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL Date & Time Functions

Test your understanding of temporal types, half-open intervals, EXTRACT, DATE_TRUNC, and date arithmetic.

Question 1 of 8Current Score: 0 / 0

What is the fundamental difference between the DATE and TIMESTAMP data types in SQL?