Chapter 20: Working with Dates and Times

Dates, times, and timestamps represent real-world events. SQL engines provide arithmetic, extraction, formatting, time zone conversion, and interval math. Favor ISO standards, store in UTC when possible, and convert at the edges when displaying to users.

Date arithmetic, extraction, and formatting

SQL supports adding and subtracting intervals, extracting fields like year or month, and formatting values for presentation. Use arithmetic for logic, use formatting only at the final presentation step.

EXTRACT and basic arithmetic

EXTRACT returns a part of a date or timestamp. Arithmetic uses intervals, or engine-specific numeric offsets. Prefer interval-aware syntax to avoid month-length surprises.

-- ANSI-style extraction
SELECT
  EXTRACT(YEAR FROM CURRENT_DATE)  AS year_num,
  EXTRACT(MONTH FROM CURRENT_DATE) AS month_num,
  EXTRACT(DAY FROM CURRENT_DATE)   AS day_num;

-- ANSI-style addition and subtraction (engines vary)
-- PostgreSQL
SELECT CURRENT_DATE + INTERVAL '7 days' AS in_seven_days;

-- MySQL / MariaDB
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY)   AS in_seven_days,
       DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

-- SQL Server
SELECT DATEADD(DAY, 7, CAST(GETDATE() AS date))       AS in_seven_days,
       DATEADD(MONTH, -1, CAST(GETDATE() AS date))    AS last_month;

DATE_TRUNC, bucketing, and grouping

Truncation rounds down to a boundary (hour, day, month). Use it for grouping facts into time buckets.

-- PostgreSQL bucketing by day and month
SELECT DATE_TRUNC('day',  created_at) AS day_bucket,  COUNT(*) FROM orders GROUP BY 1;
SELECT DATE_TRUNC('month', created_at) AS month_bucket, COUNT(*) FROM orders GROUP BY 1;

-- SQL Server: use DATETRUNC (SQL Server 2022+) or computed boundaries
SELECT DATETRUNC(day, CreatedAt) AS day_bucket, COUNT(*) FROM dbo.Orders GROUP BY DATETRUNC(day, CreatedAt);

-- MySQL: use DATE(), or format to YYYY-MM-01 for month buckets
SELECT DATE(created_at) AS day_bucket, COUNT(*) FROM orders GROUP BY DATE(created_at);
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month_bucket, COUNT(*) FROM orders GROUP BY 1;

Differences and durations

Compute elapsed time with supported constructs. Prefer interval types; if the engine returns numbers, document the unit clearly.

-- PostgreSQL: interval result
SELECT (NOW() - order_placed_at) AS elapsed FROM orders WHERE id = 42;

-- MySQL: duration in seconds or minutes
SELECT TIMESTAMPDIFF(SECOND, order_placed_at, NOW()) AS elapsed_seconds FROM orders WHERE id = 42;

-- SQL Server: DATEDIFF returns integer units
SELECT DATEDIFF(second, OrderPlacedAt, SYSDATETIME()) AS elapsed_seconds FROM dbo.Orders WHERE Id = 42;

TO_CHAR, DATE_FORMAT, FORMAT and friends

Use formatting functions at the presentation boundary. Keep internal comparisons and joins in native date types, not in strings. Patterns differ across engines.

Parsing strings into dates safely

Parsing is the inverse of formatting. Use explicit formats so inputs remain unambiguous.

-- PostgreSQL
SELECT TO_TIMESTAMP('2025-11-03 12:00', 'YYYY-MM-DD HH24:MI');

-- MySQL / MariaDB
SELECT STR_TO_DATE('2025-11-03 12:00', '%Y-%m-%d %H:%i');

-- SQL Server
SELECT TRY_CONVERT(datetime2, '2025-11-03 12:00', 120);  -- ODBC style 120 = yyyy-mm-dd hh:mi:ss

Time zones and intervals

Storing in UTC reduces ambiguity. Convert to a user’s time zone only when rendering or when the business rule depends on local time. Intervals represent human concepts like months or business days; arithmetic with intervals should respect calendar rules.

AT TIME ZONE and conversion

Engines offer explicit conversion functions. Make the source zone and target zone clear. Document whether the stored value is UTC or local time.

-- PostgreSQL: convert UTC to Europe/London at query time
SELECT (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/London' AS created_local
FROM orders;

-- PostgreSQL alternative when created_at is timestamptz in UTC
SELECT created_at AT TIME ZONE 'Europe/London' AS created_local FROM orders;

-- SQL Server
SELECT SWITCHOFFSET(TODATETIMEOFFSET(CreatedAtUtc, '+00:00'), '+01:00') AS created_local
FROM dbo.Orders;

-- MySQL 8+: convert between zones
SELECT CONVERT_TZ(created_at_utc, '+00:00', 'Europe/London') AS created_local
FROM orders;

Intervals vs absolute time shifts

Intervals represent amounts like one month or two days. Absolute shifts like adding 86,400 seconds can be wrong around daylight saving transitions. Prefer calendar-aware intervals.

-- PostgreSQL: calendar-aware
SELECT (DATE '2025-03-28' + INTERVAL '5 days')  AS plus_5d,
       (TIMESTAMP '2025-03-31 23:30 Europe/London' AT TIME ZONE 'Europe/London')
         + INTERVAL '1 day' AS next_day_local;

-- SQL Server: add calendar units
SELECT DATEADD(day, 5, CAST('2025-03-28' AS date)) AS plus_5d;

-- MySQL: INTERVAL expression
SELECT DATE_ADD('2025-03-28', INTERVAL 5 DAY) AS plus_5d;

Normalizing to day boundaries

Many reports need local-day buckets. Convert to the target zone first, then truncate.

-- PostgreSQL
SELECT DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/London') AS day_local, COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;

-- SQL Server (2022+)
SELECT DATETRUNC(day, SWITCHOFFSET(TODATETIMEOFFSET(CreatedAtUtc, '+00:00'), '+01:00)) AS day_local,
       COUNT(*)
FROM dbo.Orders
GROUP BY DATETRUNC(day, SWITCHOFFSET(TODATETIMEOFFSET(CreatedAtUtc, '+00:00'), '+01:00))
ORDER BY day_local;

-- MySQL
SELECT DATE(CONVERT_TZ(created_at_utc, '+00:00', 'Europe/London')) AS day_local, COUNT(*)
FROM orders
GROUP BY day_local
ORDER BY day_local;

Generating date ranges and calendars

Create ranges to fill gaps in reports. Use set-generating constructs or helper tables.

-- PostgreSQL: generate_series over dates
SELECT d::date AS dt
FROM generate_series(DATE '2025-10-01', DATE '2025-10-31', INTERVAL '1 day') AS g(d);

-- SQL Server: tally using recursive CTE
WITH d AS (
  SELECT CAST('2025-10-01' AS date) AS dt
  UNION ALL
  SELECT DATEADD(day, 1, dt) FROM d WHERE dt < '2025-10-31'
)
SELECT dt FROM d OPTION (MAXRECURSION 0);

-- MySQL: recursive CTE (8.0+)
WITH RECURSIVE d AS (
  SELECT DATE('2025-10-01') AS dt
  UNION ALL
  SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM d WHERE dt < '2025-10-31'
)
SELECT dt FROM d;

Common pitfalls and checks

Validate inputs, document assumptions, and add constraints where the engine allows them.

• Reject impossible dates at ingest with a CHECK like created_at_utc >= TIMESTAMP '2000-01-01'.
• Avoid mixing local times from different time zones in one column without an explicit zone or separate columns.
• Store durations as intervals or integers in clear units (seconds, minutes); name the column accordingly.