Timezone handling is correct in report date bucketing

SQL date bucketing with DATE(occurred_at) uses PostgreSQL's session timezone — typically UTC — without advertising that fact to users. A campaign sent at 11pm EST fires tracking events that land on the following UTC day, making the performance report look like the campaign ran a day later than it did. For teams optimizing by day-of-week or comparing week-over-week performance, these bucketing artifacts corrupt trend analysis. The data-integrity taxon names the failure: the data is accurate at the row level but the aggregation step introduces systematic misclassification that is invisible without timezone awareness.

Use an explicit AT TIME ZONE clause in every date-bucketing query. Never rely on the session timezone default:

-- Parameterized with account timezone
SELECT
  (occurred_at AT TIME ZONE $2)::DATE AS date,
  COUNT(*) AS event_count
FROM email_events
WHERE campaign_id = $1
GROUP BY 1
ORDER BY 1;

In your ORM layer:

// src/lib/reporting/queries.ts
const rows = await db.$queryRaw<{ date: string; count: number }[]>`
  SELECT
    (occurred_at AT TIME ZONE ${accountTimezone})::DATE AS date,
    COUNT(*) AS count
  FROM email_events
  WHERE campaign_id = ${campaignId}
  GROUP BY 1
  ORDER BY 1
`

Store a per-account reporting_timezone field (e.g., 'America/New_York') and pass it to all reporting queries. Document in the UI which timezone reports are displayed in.

• ID: campaign-analytics-attribution.reporting-pipeline.timezone-handling

• Severity: low

• What to look for: Examine how report date bucketing works — when a report groups events by day, which timezone is used to determine "day"? Look for SQL queries that use DATE(occurred_at) without a timezone conversion (which defaults to UTC), AT TIME ZONE clauses, or application-level timezone handling. For email campaigns, reporting in the account's local timezone versus UTC produces different results for sends near midnight. Also check whether the reporting timezone is configurable per account or hard-coded.

• Pass criteria: Date bucketing in reports uses an explicit timezone (either UTC consistently across all reports, or the account's configured timezone). The timezone used for bucketing is documented or displayed on reports. Count all date-bucketing queries and verify at least 100% include an explicit timezone clause.

• Fail criteria: Date bucketing uses DATE(occurred_at) without timezone conversion, implicitly using PostgreSQL's session timezone (which may differ from the user's expectation). Different reports use different implicit timezones.

• Skip (N/A) when: All reporting uses UTC exclusively and this is clearly communicated to users, or reports do not bucket by date.

• Detail on fail: Example: "SQL uses DATE(occurred_at) without AT TIME ZONE — buckets events in UTC, causing sends at 11pm EST to appear on the next day in reports" or "No timezone conversion in daily aggregation queries"

• Remediation: Use explicit timezone conversion in date bucketing:

  -- Bad: implicit UTC bucketing
  SELECT DATE(occurred_at) AS date, COUNT(*) FROM email_events GROUP BY 1;
  
  -- Good: explicit timezone conversion
  SELECT
    (occurred_at AT TIME ZONE 'America/New_York')::DATE AS date,
    COUNT(*) AS event_count
  FROM email_events
  WHERE campaign_id = $1
  GROUP BY 1
  ORDER BY 1;
  
  -- Or parameterized with account timezone:
  SELECT
    (occurred_at AT TIME ZONE $2)::DATE AS date,
    COUNT(*) AS event_count
  FROM email_events
  WHERE campaign_id = $1
  GROUP BY 1
  ORDER BY 1;
  

  // In your ORM layer
  const events = await db.$queryRaw`
    SELECT (occurred_at AT TIME ZONE ${accountTimezone})::DATE AS date, COUNT(*) AS count
    FROM email_events
    WHERE campaign_id = ${campaignId}
    GROUP BY 1
    ORDER BY 1
  `