Dates & times
Truncate, extract, add intervals, and bucket by period — with the dialect differences that trip everyone up.
Date handling is the least portable part of SQL. The concepts below are
universal; the function names are not. The runnable examples use SQLite
(date(), strftime()); the Postgres / other equivalents are noted inline.
Now
| Current date | CURRENT_DATE (standard) · date('now') (SQLite) |
| Current timestamp | CURRENT_TIMESTAMP · now() (Postgres) · GETDATE() (SQL Server) |
Bucket by period — DATE_TRUNC
Group events into months, weeks, days. This is how you build time series.
SQL playground
Loading editor…
⌘/Ctrl + Enter
Postgres: date_trunc('month', order_date). SQL Server: DATETRUNC(month, order_date) (2022+) or DATEADD(month, DATEDIFF(month, 0, order_date), 0).
Pull out a field — EXTRACT
SQL playground
Loading editor…
⌘/Ctrl + Enter
Standard: EXTRACT(MONTH FROM order_date), EXTRACT(DOW FROM order_date).
Date arithmetic
SQL playground
Loading editor…
⌘/Ctrl + Enter
- Postgres:
order_date + INTERVAL '7 days',date_trunc('month', order_date). - MySQL:
DATE_ADD(order_date, INTERVAL 7 DAY). - SQL Server:
DATEADD(day, 7, order_date).
Difference between two dates
SQL playground
Loading editor…
⌘/Ctrl + Enter
Standard-ish: age(a, b) / a - b (Postgres), DATEDIFF(day, b, a) (SQL Server),
DATEDIFF(a, b) (MySQL, in days).
Gotchas
- Strings that look like dates aren't dates.
'2026-13-99'compares fine as text and blows up as a date. Store dates in a real date/timestamp type. - Time zones.
TIMESTAMPvsTIMESTAMPTZin Postgres; store UTC, convert at the edge. - Half-open ranges beat
BETWEENfor timestamps:ts >= '2026-01-01' AND ts < '2026-02-01'—BETWEENincludes the upper bound and misses23:59:59.999. - Week start differs by locale (Sunday vs Monday) and by function.
- WHERE — half-open range filtering for timestamps.
- Running totals & moving averages — time series built on monthly buckets.
- GROUP BY — grouping by a truncated date.