# Timestamps off by hours: timezone, UTC and DST conversion mistakes

**Code:** `timezone` · **Category:** SQL · **Seen on:** SQL, PostgreSQL, MySQL

Stored times appear shifted by 5 or 8 hours, or events shift around daylight saving changes.

## Meaning

Store timestamps in UTC (`timestamptz` in PostgreSQL, UTC `datetime` elsewhere), convert at display time with the user's zone, and know the difference between `timestamp` and `timestamp with time zone`. Server, session and client timezones all matter.

## Common causes

- Mixing local time and UTC in one column
- `timestamp without time zone` interpreted as local
- Session timezone differing from the app's
- DST transition (ambiguous/non-existent hour)
- `NOW()` returning server local time

## Quick fix

1. Store UTC and convert at the edge
2. Use `timestamptz` / `AT TIME ZONE`
3. Set the connection time zone consistently

## Detailed fix by platform

### SQL

1. `SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' FROM events;`

## How to diagnose

1. **Column** — Timestamp with time zone?
2. **Session TZ** — Known?
3. **DST** — Considered?

---
Source: [WhatIsMyError](https://whatismyerror.com/errors/sql/timezone-conversion-off-by-hours)
