Time & dates

Half-open intervals, truncate vs extract, timezones/DST, date spines, week/month boundaries.

• ★Half-open intervals

  Use [start, end) for timestamp ranges — BETWEEN loses the last day's daytime (P01 atom, promoted to habit).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sessions(id INT, ended TIMESTAMP); INSERT INTO sessions VALUES (1, TIMESTAMP '2024-06-30 14:00'), (2, TIMESTAMP '2024-07-01 00:00'), (3, TIMESTAMP '2024-06-10 09:00');

  The trap

  SELECT id FROM sessions WHERE ended BETWEEN TIMESTAMP '2024-06-01' AND TIMESTAMP '2024-06-30' ORDER BY id;

  3

  The fix

  SELECT id FROM sessions WHERE ended >= TIMESTAMP '2024-06-01' AND ended < TIMESTAMP '2024-07-01' ORDER BY id;

  1

  3

  The June parking report drops every session that ended after midnight on the 30th — BETWEEN's closed upper bound cuts the last day short.

• Truncate vs extract

  date_trunc('month', ...) groups within a month; EXTRACT(month ...) merges Januaries across years.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE gigs(paid_on DATE, fee INT); INSERT INTO gigs VALUES (DATE '2024-01-10',100),(DATE '2025-01-05',150),(DATE '2025-02-08',60);

  The trap

  SELECT EXTRACT(month FROM paid_on) AS m, SUM(fee) AS fees FROM gigs GROUP BY m ORDER BY m;

  1	250
  2	60

  The fix

  SELECT strftime(date_trunc('month', paid_on), '%Y-%m') AS m, SUM(fee) AS fees FROM gigs GROUP BY date_trunc('month', paid_on) ORDER BY 1;

  2024-01	100
  2025-01	150
  2025-02	60

  Grouping by EXTRACT(month) merged every year's January into one row — the two Januaries summed to 250, where date_trunc keeps 2024-01 and 2025-01 as separate months.

• Timezones & DST

  Stored-UTC vs displayed-local shifts 'daily' metrics by the TZ choice, and DST gives 23/25-hour days.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE soaks(guest VARCHAR, in_utc TIMESTAMPTZ, out_utc TIMESTAMPTZ); INSERT INTO soaks VALUES ('g1', TIMESTAMPTZ '2027-03-14 06:30:00+00', TIMESTAMPTZ '2027-03-14 07:00:00+00');

  The trap

  SELECT guest, CAST(date_diff('minute', in_utc AT TIME ZONE 'America/New_York', out_utc AT TIME ZONE 'America/New_York') AS INT) AS mins FROM soaks;

  g1

  90

  The fix

  SELECT guest, CAST(date_diff('minute', in_utc, out_utc) AS INT) AS mins FROM soaks;

  g1

  30

  The soak session read 90 minutes, not 30 — subtracting two naive local wall-clock times across the spring-forward night counts the skipped 02:00 hour; computing on UTC instants gives the true duration.

• Date spine generation

  generate_series / a calendar table fills gap-free reporting where data has missing days.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE fees(d DATE, amount INT); INSERT INTO fees VALUES (DATE '2024-02-02',80);

  The trap

  SELECT strftime(d, '%m-%d') AS day, SUM(amount) AS total FROM fees GROUP BY d ORDER BY d;

  02-02

  80

  The fix

  SELECT strftime(t.g, '%m-%d') AS day, COALESCE(SUM(f.amount), 0) AS total FROM generate_series(DATE '2024-02-01', DATE '2024-02-04', INTERVAL 1 DAY) AS t(g) LEFT JOIN fees f ON f.d = t.g GROUP BY t.g ORDER BY t.g;

  02-01	0
  02-02	80
  02-03	0
  02-04	0

  The timeline skipped the quiet days — a bare GROUP BY only produces rows for dates that have data, so three zero-enrolment days simply vanished until a generated spine LEFT JOINed them back.

• Week / month boundaries

  ISO week vs engine default, and month arithmetic at the 31st, both shift boundaries silently.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE harvest(d DATE, kg INT); INSERT INTO harvest VALUES (DATE '2027-01-01', 40);

  The trap

  SELECT EXTRACT(year FROM d) AS yr, week(d) AS wk, SUM(kg) AS kg FROM harvest GROUP BY yr, wk ORDER BY yr, wk;

  2027

  53

  40

  The fix

  SELECT isoyear(d) AS iso_yr, week(d) AS wk, SUM(kg) AS kg FROM harvest GROUP BY iso_yr, wk ORDER BY iso_yr, wk;

  2026

  53

  40

  Grouping the New Year harvest by (calendar year, week) booked it into week 53 of 2027 — ISO week 53 belongs to ISO year 2026, so the calendar year and the ISO week don't pair; isoyear does.

• Month arithmetic clamps and is non-associative

  Adding months clamps to month-end when the day doesn't exist (Jan 31 -> Feb 28), and clamping doesn't undo — plus one month twice is not plus two months.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE anchor(d DATE); INSERT INTO anchor VALUES (DATE '2025-01-31');

  The trap

  SELECT strftime(d + INTERVAL 1 MONTH + INTERVAL 1 MONTH, '%Y-%m-%d') AS renewal FROM anchor;

  2025-03-28

  The fix

  SELECT strftime(d + INTERVAL 2 MONTH, '%Y-%m-%d') AS renewal FROM anchor;

  2025-03-31

  The subscription anchored Jan 31 decayed to the 28th — adding a month clamps Jan 31 to Feb 28, and the clamp is lossy, so hopping month by month walks the date permanently down; a single plus-two-months from the anchor keeps Mar 31.

• Epoch units (seconds vs milliseconds)

  An epoch number carries no units — seconds, milliseconds, and micros are just magnitudes — so the wrong unit produces a valid timestamp in the wrong millennium, no error.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE logs(id INT, ts_ms BIGINT); INSERT INTO logs VALUES (1, 1700000000000);

  The trap

  SELECT id, strftime(to_timestamp(ts_ms), '%Y') AS yr FROM logs;

  1

  55840

  The fix

  SELECT id, strftime(to_timestamp(ts_ms / 1000), '%Y-%m') AS ym FROM logs;

  1

  2023-11

  A millisecond epoch fed to a seconds converter landed in the year 55840 — an epoch number carries no units, so the wrong magnitude produces a valid timestamp in the wrong millennium, no error; dividing by 1000 first lands it in Nov 2023.

• date_diff counts boundaries, not duration

  date_diff('day', a, b) counts boundary crossings, not elapsed time — two minutes across midnight is 1 day; 23 hours inside one day is 0.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sess(a TIMESTAMP, b TIMESTAMP); INSERT INTO sess VALUES (TIMESTAMP '2027-01-01 23:59', TIMESTAMP '2027-01-02 00:01');

  The trap

  SELECT date_diff('day', a, b) AS nights FROM sess;

  1

  The fix

  SELECT date_diff('minute', a, b) AS mins FROM sess;

  2

  A two-minute session across midnight billed as a full night — date_diff('day', …) counts boundary crossings (one midnight = 1), not elapsed time; subtraction (or date_diff in minutes) measures the real 2 minutes.