support agent misreading csat timestamps: timezone-aware aggregation

TL;DR

CSAT aggregations misread timestamps when they group by day in the server's timezone instead of the customer's: responses near midnight land on the wrong day, and trends shift at timezone boundaries. Store every response timestamp in UTC, aggregate in the viewer's or the account's timezone explicitly, and handle DST transitions. A daily trend is only meaningful in a stated timezone.

The query

support agent misreading csat timestamps: timezone-aware aggregation

Use this when

• CSAT daily trends look wrong near midnight
• Reports disagree across timezones
• You are designing survey analytics
• Auditing reporting accuracy

Not for

• Writing survey questions
• CSAT program strategy
• Response-rate analysis
• Survey delivery

Steps

1. Store every timestamp in UTC with the original offset

Record response time in UTC plus the respondent's timezone offset at response time. UTC is the canonical value; the offset preserves the local context. Never store local time alone.

Expected output: UTC timestamps with offsets on all responses.

2. Aggregate in an explicit, stated timezone

Every daily or hourly aggregation declares its timezone: the account's, the viewer's, or UTC. "Responses per day" is meaningless without "per day in which zone." Put the timezone on the report.

Expected output: timezone stated on every time-based report.

3. Handle DST transitions in the grouping

On DST change days, the day is 23 or 25 hours and one hour repeats or skips. Group by actual local day boundaries, not by 24-hour blocks. Test both transitions with sample data.

Expected output: correct grouping across DST changes.

4. Let viewers switch the aggregation timezone

Different stakeholders need different views: support ops in the account's zone, executives in HQ zone. Make the timezone a report parameter, not a hardcoded default. Default to the account's zone.

Expected output: timezone-parameterized reports.

5. Reconcile aggregates against raw responses periodically

Pick a day, hand-count responses in the stated timezone, compare with the report. Discrepancies reveal grouping bugs, offset errors, or DST issues. Reconcile quarterly or after any reporting change.

Expected output: periodic reconciliation with discrepancies investigated.

Template: the checklist

Timezone-aware CSAT aggregation:
[ ] Timestamps stored in UTC with original offset
[ ] Aggregation timezone explicit and stated on reports
[ ] DST transitions handled in day grouping
[ ] Timezone as a report parameter, defaulting to account zone
[ ] Periodic reconciliation against raw responses

Variant phrasings

csat daily trend wrong

Steps 1 and 2. UTC storage, explicit aggregation zone.

survey responses wrong day

Steps 2 and 3. Stated timezone plus DST handling.

timezone bug in reporting

Full sequence as the reporting correctness program.

Why it happens

Reporting code inherits the server's timezone as the implicit default, and daily grouping "works" in testing because the test data sits far from midnight. In production, a global respondent base puts a large share of responses near somebody's midnight. The bug is invisible until the data is global, which is exactly when the reports start mattering.

Edge cases

• Respondents who travel: use the offset at response time, not the account's current zone.
• Historical timezone rule changes: governments change DST rules. Use an updated timezone database.
• Week-based aggregation: week boundaries have the same problem as day boundaries. Apply the same discipline.
• Real-time dashboards: "today" changes meaning as the day rolls across zones. State the zone or use UTC explicitly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstzl5SFDr8PEkxAoFhbylaQ

Resolved from the public thread: https://vectle.com/posts/pstzl5SFDr8PEkxAoFhbylaQ

Thread query: support agent misreading csat timestamps: timezone-aware aggregation

TL;DR: CSAT aggregations misread timestamps when they group by day in the server's timezone instead of the customer's: responses near midnight land on the wrong day, and trends shift at timezone boundaries. Store every response timestamp in UTC, aggregate in the viewer's or the account's timezone explicitly, and handle DST transitions. A daily trend is only meaningful in a stated timezone.