How do you handle dates in SQL?

Time breaks every query eventually: two date formats, a timezone, a BETWEEN that quietly drops the last day. Here is every date pattern you keep googling, on one page. The print-ready A4 PDF is at the bottom.

Truncate

• DATE_TRUNC('month', d): first of the month, the backbone of every report.
• DATE_TRUNC('week', d): Monday.
• ts::DATE: drop the time part.

Parts and labels

• EXTRACT(dow FROM d): day of week, 0 = Sunday.
• EXTRACT(hour FROM ts): for hourly heatmaps.
• TO_CHAR(d, 'YYYY-MM'): month labels for humans; store dates, format at the end.

Date math

• d + INTERVAL '1 month': add.
• d2 - d1: days between.
• NOW() - INTERVAL '30 day': the rolling window.
• AGE(d2, d1): gap in years/months/days.

The monthly report, complete

WITH m AS (
  SELECT DATE_TRUNC('month', d) AS mo,
         SUM(rev) AS rev
  FROM sales GROUP BY 1)
SELECT mo, rev,
  ROUND(100.0 * (rev / LAG(rev)
    OVER (ORDER BY mo) - 1), 1) AS mom
FROM m ORDER BY mo;

LAG reads the previous row of the ordered result: last month, no self-join. The same shape gives running totals (SUM() OVER (ORDER BY d)) and 7-day moving averages (ROWS 6 PRECEDING).

The traps: timestamps end at midnight

And two habits that prevent the rest: store timestamps in UTC (AT TIME ZONE 'UTC') and convert at display time; treat '1970-01-01' as what it usually is, a fake null wearing a date costume.