My time-series charts had holes: any hour with no events simply didn't exist in the query result, so the line chart connected across gaps and hid outages. The naive "group by hour" prompt never generated the missing rows.
The prompt that fixed it makes the model build a date spine with generate_series and left-join the aggregates onto it, so every bucket exists with a 0 (or null, my choice) for empty periods. It also became strict about timezone handling, which was quietly shifting my daily buckets.
Running this against Postgres with a local model. How do you keep the model from silently switching to database-specific functions that don't exist on my version?
Write a single Postgres query that returns chart-ready time buckets. Requirements:
1. Build a continuous date spine with generate_series over [{START}, {END}] at a {GRAIN} interval (e.g. '1 hour'). LEFT JOIN the aggregates onto it so EVERY bucket is present.
2. Empty buckets: return 0 for counts and NULL for averages (never omit the row). State this choice in a comment.
3. Timezone: bucket in {TZ} explicitly via `date_trunc('{GRAIN}', ts AT TIME ZONE {TZ})`; do not rely on the session timezone.
4. Output columns exactly: bucket (timestamptz), n (count), value (the metric). Ordered by bucket ascending.
5. Portability: target Postgres 13+. Do NOT use functions newer than 13 (call out if you would). No vendor extensions.
Before the SQL, list the assumptions (table name, timestamp column, metric expression) as {PLACEHOLDERS} so I can confirm them. After the SQL, add a one-line note on the expected row count so I can sanity-check that the spine is complete.