Index-friendly date range filtering

-- PostgreSQL: sargable date filters that keep an index on created_at usable.
-- Rule: compare the indexed column directly, never wrap it in a function.

-- Good: half-open range covers all of March 2024 and no other month.
SELECT id, total, created_at
FROM orders
WHERE created_at >= DATE '2024-03-01'
  AND created_at <  DATE '2024-04-01'
ORDER BY created_at;

-- Bad: DATE(created_at) = ... forces a scan; keep the query above instead.

-- Matching index for equality and range lookups plus the sort.
CREATE INDEX IF NOT EXISTS orders_created_at_idx
  ON orders (created_at);

-- "Today" report: bound both ends so the planner sees a range.
SELECT count(*) AS orders_today
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at <  CURRENT_DATE + INTERVAL '1 day';