SQL Date and Time Analysis
Time is the most common analytical dimension: filtering by periods, grouping by day/week/month, measuring durations and ages, and handling time zones.
Concept
Time is the most common analytical dimension: filtering by periods, grouping by day/week/month, measuring durations and ages, and handling time zones. Date functions differ significantly between DBMSs (Module 10 lists them); this module focuses on the patterns.
Why It Matters
Off-by-one date errors, time-zone mistakes, and inclusive/exclusive range bugs are among the most frequent causes of wrong reports and failing tests.
Syntax
Filter with half-open ranges: ts >= start AND ts < end.
Keep the column bare in WHERE (sargable); do arithmetic on the constants.
Store instants in UTC or with time zone; convert for display.
Group by a truncated date key.
Example
38.1 Date filtering and date ranges 38.2 Current date, previous date, and period boundaries
For 2026-09-29 the PostgreSQL query returns: today 2026-09-29, yesterday 2026-09-28, month_start 2026-09-01, month_end 2026-09-30, prev_month_start 2026-08-01, year_start 2026-01-01.
38.3 Daily, weekly, monthly, and yearly aggregation (2026-01-18 is a Sunday, so it belongs to the week starting Monday 2026-01-12; 2026-03-01 is a Sunday, week of 2026-02-23.)
Weeks with no orders are simply absent. To show zero rows, LEFT JOIN from a generated week series:
38.4 Date difference and aging calculations Aging of open (not delivered, not cancelled) orders as of 2026-03-31:
Aging buckets are standard in accounts receivable, open defects, DTC age, and support tickets.
Age in full years (birthday-aware):
38.5 Time intervals and timestamp handling Overlapping intervals (e.g., bookings, shifts, ECU test-bench reservations):
PostgreSQL can enforce non-overlap in the database with range types and an exclusion constraint:
Truncating timestamps to hours/minutes for time-series aggregation:
38.6 Time zones TIMESTAMPTZ (stored as UTC) ts AT TIME ZONE 'Asia/Kolkata'
TIMESTAMP (converted using session time_zone) CONVERT_TZ(ts, 'UTC', 'Asia/Kolkata') (needs time-zone tables loaded)
ts AT TIME ZONE 'India Standard Time' (Windows zone names)
TIMESTAMP WITH TIME ZONE, WITH LOCAL TIME ZONE FROM_TZ(ts, 'UTC') AT TIME ZONE 'Asia/Kolkata' None (store UTC text or epoch)
datetime(ts, 'localtime') or '+05:30' modifiers Daily reports "in local time" must convert before truncating to the day, or orders near midnight land on the wrong date:
Daylight saving time: some zones skip or repeat an hour; always use named zones (Europe/Berlin), not fixed offsets, when converting historical data.
-- Orders in February 2026 (works for DATE and TIMESTAMP columns)
SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE '2026-02-01'
AND order_date < DATE '2026-03-01';-- Last 7 days relative to today
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days' -- PostgreSQL
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) -- MySQL
WHERE order_date >= DATEADD(day, -7, CAST(GETDATE() AS DATE)) -- SQL Server
WHERE order_date >= TRUNC(SYSDATE) - 7 -- Oracle
WHERE order_date >= date('now', '-7 days') -- SQLite-- PostgreSQL
SELECT CURRENT_DATE AS today,
CURRENT_DATE - 1 AS yesterday,
DATE_TRUNC('month', CURRENT_DATE)::DATE AS month_start,
(DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::DATE AS month_end,
DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')::DATE AS prev_month_start,
DATE_TRUNC('year', CURRENT_DATE)::DATE AS year_start;
-- SQL Server
SELECT CAST(GETDATE() AS DATE) AS today,
DATEADD(day, -1, CAST(GETDATE() AS DATE)) AS yesterday,
DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS month_start,
EOMONTH(GETDATE()) AS month_end;
-- MySQL
SELECT CURDATE(), CURDATE() - INTERVAL 1 DAY, DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE());
-- Oracle
SELECT TRUNC(SYSDATE), TRUNC(SYSDATE) - 1, TRUNC(SYSDATE, 'MM'), LAST_DAY(TRUNC(SYSDATE)) FROM dual;-- Weekly (ISO weeks start on Monday)
SELECT DATE_TRUNC('week', order_date)::DATE AS week_start,
COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;
-- MySQL: DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY)
-- SQL Server: DATEADD(day, -((DATEPART(weekday, order_date) + @@DATEFIRST - 2) % 7), order_date)
-- or DATETRUNC(iso_week, order_date) in SQL Server 2022+
-- Oracle: TRUNC(order_date, 'IW')-- PostgreSQL
SELECT w::DATE AS week_start, COUNT(o.order_id) AS orders
FROM generate_series(DATE '2026-01-05', DATE '2026-03-09', INTERVAL '7 days') AS w
LEFT JOIN orders o ON o.order_date >= w AND o.order_date < w + INTERVAL '7 days'
GROUP BY w
ORDER BY w;SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;SELECT order_id, status, order_date,
DATE '2026-03-31' - order_date AS age_days, -- MySQL: DATEDIFF('2026-03-31', order_date)
CASE
WHEN DATE '2026-03-31' - order_date <= 30 THEN '0-30'
WHEN DATE '2026-03-31' - order_date <= 60 THEN '31-60'
WHEN DATE '2026-03-31' - order_date <= 90 THEN '61-90'
ELSE '90+'
END AS aging_bucket
FROM orders
WHERE status NOT IN ('DELIVERED', 'CANCELLED')
ORDER BY age_days DESC;-- PostgreSQL
EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth))
-- MySQL
TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())
-- SQL Server (DATEDIFF(year) counts boundaries, so correct for birthdays not yet reached)
DATEDIFF(year, date_of_birth, GETDATE())
- CASE WHEN DATEADD(year, DATEDIFF(year, date_of_birth, GETDATE()), date_of_birth) > GETDATE() THEN 1 ELSE 0 END
-- Oracle
FLOOR(MONTHS_BETWEEN(SYSDATE, date_of_birth) / 12)-- PostgreSQL: difference between timestamps is an INTERVAL
SELECT TIMESTAMP '2026-02-06 09:00' - TIMESTAMP '2026-02-05 14:20' AS diff; -- 18:40:00
SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2026-02-06 09:00' - TIMESTAMP '2026-02-05 14:20')) / 60 AS minutes; -- 1120
-- MySQL
SELECT TIMESTAMPDIFF(MINUTE, '2026-02-05 14:20:00', '2026-02-06 09:00:00'); -- 1120
-- SQL Server
SELECT DATEDIFF(MINUTE, '2026-02-05 14:20', '2026-02-06 09:00'); -- 1120
-- Oracle: TIMESTAMP - TIMESTAMP = INTERVAL DAY TO SECOND; DATE - DATE = days (fraction)
SELECT (DATE '2026-02-06' - DATE '2026-02-05') * 24 * 60 FROM dual; -- 1440-- Two intervals [a_start, a_end) and [b_start, b_end) overlap when:
WHERE a.start_ts < b.end_ts AND b.start_ts < a.end_tsExpected Result
SELECT EXTRACT(MONTH FROM order_date) AS m,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order,
MAX(order_date) - MIN(order_date) AS span_days
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY m;Common Mistakes
BETWEEN '2026-01-01' AND '2026-01-31' on timestamps (misses Jan 31 after midnight).
WHERE EXTRACT(YEAR FROM ts) = 2026 on large tables (non-sargable).
Mixing UTC and local timestamps in one column.
Using SQL Server DATEDIFF(year, ...) as age.
Truncating to days before converting time zones.
Relying on server default date formats instead of ISO literals.
Best Practices
Half-open ranges everywhere.
UTC storage, local display.
A calendar/date dimension table for business days, holidays, fiscal periods.
Consistent week definition (ISO Monday vs US Sunday) documented in reports.
Interview Relevance
High: "Orders in the last 30 days", "monthly/weekly report", "difference between two timestamps", "customer age", "handle time zones", "why BETWEEN is dangerous with timestamps".
Practice Questions
Count orders per weekday name.
Find employees whose work anniversary falls in the next 30 days.
Compute average days between consecutive orders per customer.
Build a 0–30/31–60/61–90/90+ aging report for active DTCs as of 2026-03-31.
Convert UTC event timestamps to Europe/Berlin and count events per local day.