Here’s a bug that’ll waste your afternoon: your reporting tool lets users pick a timezone, but the results are wrong. You’re filtering correctly but displaying UTC data.

The problem

Say a user in Pacific time requests data for August 25th. Your query looks like this:

SELECT
    toDate(dt) AS dt_date,
    sum(revenue) AS total_revenue
FROM events
WHERE
    toTimeZone(dt, 'America/Los_Angeles') >= '2025-08-25'
    AND toTimeZone(dt, 'America/Los_Angeles') < '2025-08-26'
GROUP BY dt_date
ORDER BY dt_date

The WHERE clause filters correctly using the user’s timezone. But toDate(dt) in the SELECT uses the raw UTC timestamp. You’re showing them UTC dates for data that was filtered by Pacific time.

At 11:45 PM Pacific on the 25th, pulling a report “for the 25th” shows data from the 26th UTC. Users will report this as a bug, and they’re right.

The fix

Use a CTE to convert the timestamp once, then use that converted value everywhere:

WITH toTimeZone(dt, 'America/Los_Angeles') AS dt_tz
SELECT
    toDate(dt_tz) AS dt_date,
    sum(revenue) AS total_revenue
FROM events
WHERE
    dt_tz >= '2025-08-25'
    AND dt_tz < '2025-08-26'
GROUP BY dt_date
ORDER BY dt_date

Now dt_tz is used for both filtering and display. The dates users see match what they asked for.

The pattern

Whenever you’re doing timezone-aware reporting:

  1. Convert once at the top with a CTE
  2. Use that converted value everywhere - filters, grouping, and output
  3. Never mix raw UTC and converted values in the same query

This also simplifies the query - less repetition of the timezone conversion, fewer places to make mistakes.