Timezones and wrong local time in Postgres

What you see

• A booking made for 09:00 shows as 08:00 or 14:00 in the app.
• now() in the SQL editor doesn't match your clock.
• A daily report groups late-evening orders into the next day.

Why it happens

Postgres has two timestamp types, and they behave very differently:

Most "wrong time" bugs come from one of these:

1. Using timestamp. Your app sends 2026-11-20T09:00:00+01:00; a timestamp column throws away the +01:00, so the value no longer says which 09:00 it was.
2. Converting twice. The server converts to local time, then the browser converts again.
3. Grouping by date in UTC. date(created_at) uses the session timezone, not your user's.

To see what your session uses:

show timezone;
select now(), now() at time zone 'Europe/Paris' as paris_wall_clock;

How to fix it

1. Store instants as timestamptz.

alter table public.bookings
  alter column starts_at type timestamptz
  using starts_at at time zone 'UTC';

The using clause says how to read the old values. Use 'UTC' if your app wrote UTC into them, or the zone it actually used. On a large table this rewrites every row; see ALTER TABLE takes forever.

2. Send ISO 8601 with an offset, and convert only for display.

await supabase.from('bookings').insert({
  room_id: 3,
  starts_at: new Date('2026-11-20T09:00:00+01:00').toISOString(), // stored as 08:00 UTC
})

const { data } = await supabase.from('bookings').select('starts_at')
const label = new Date(data![0].starts_at).toLocaleString('fr-FR', { timeZone: 'Europe/Paris' })

The Data API returns timestamptz values with an offset, so the browser can convert them correctly.

3. Group by the user's local day in SQL with at time zone:

select (created_at at time zone 'America/New_York')::date as local_day,
       count(*) as orders
from public.orders
group by 1
order by 1;

For per-user zones, store an IANA name such as Europe/London on the profile (not a fixed offset like +01:00, which breaks across daylight saving changes) and pass it in.

4. Use timestamp or date only for wall-clock values that aren't an instant, like "the shop opens at 09:00" or a birthday.

Changing the timezone setting

You can set it for one session or transaction (set timezone = 'Asia/Tokyo';) to make output easier to read. Avoid changing it at the role level: on SupaHost all projects share one Postgres server, so a role setting like alter role postgres set timezone ... affects every project on the instance. Converting in queries or in the app is safer.

On SupaHost

• Cron schedules in the Cron tab and in cron.schedule() are in UTC. A job meant for 03:00 in New York needs 0 8 * * * in winter and 0 7 * * * in summer.
• The maintenance window for automatic upgrades, 03:00-05:00, is also UTC.

Related

• Tables, columns and data
• Cron syntax and examples
• ALTER TABLE takes forever on a large table