# Timezones and wrong local time in Postgres

> Times off by a few hours usually come from timestamp without time zone, or converting twice. How to store times as timestamptz, convert for display, and group by a user's local day.

Source: https://supahost.app/kb/timezones · Updated 2026-11-11

## 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:

| Type | What it stores | What you get back |
|---|---|---|
| `timestamptz` (`timestamp with time zone`) | An exact instant, kept internally as UTC | That instant, shown in the session's `timezone` setting |
| `timestamp` (`timestamp without time zone`) | A date and clock time with no zone | Exactly what was stored, whatever zone the reader is in |

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:

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

## How to fix it

**1. Store instants as `timestamptz`.**

```sql
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](/kb/slow-alter-table).

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

```ts
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`:

```sql
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](/docs/database/tables)
- [Cron syntax and examples](/docs/cron-queues/cron-syntax)
- [ALTER TABLE takes forever on a large table](/kb/slow-alter-table)