Convert Time Zones in SQL

Example query

This example assumes: orders(id, created_at timestamptz, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT (created_at AT TIME ZONE 'Europe/London')::date AS local_day,
       COUNT(*) AS orders,
       SUM(total_amount) AS sales
FROM orders
GROUP BY 1
ORDER BY 1;

How it works

• For a timestamptz column, AT TIME ZONE 'Europe/London' returns the wall-clock time in London, including daylight saving.
• Casting that result to date gives the local calendar day.
• Grouping by the local day means an order at 23:30 UTC in summer lands on the next London day, as the business would expect.

MySQL, BigQuery, SQL Server and SQLite differences

• For a timestamp without time zone that holds UTC, convert twice: created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London'.
• SQL Server 2016+: created_at AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'. BigQuery: DATE(created_at, 'Europe/London'). MySQL: CONVERT_TZ, which needs the time zone tables loaded.

Common mistakes

• Mixing up timestamp and timestamptz converts in the wrong direction; check the column type first.
• Use region names such as Europe/London, not fixed offsets like +01:00, or daylight saving will be wrong half the year.
• Daily totals change when you move from UTC to local time; tell readers which one a report uses.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show orders by day in London time instead of UTC.
• How many orders did we get between 9am and 5pm local time?