Time zones, UTC and timestamps

A timestamp can mean two different things. It can name a moment, the same instant everywhere in the world, or it can be a clock reading, what a wall clock showed somewhere unstated. Applications store moments in UTC, the world’s reference time, and convert them to a local zone only at the edges: when a person types a time in, and when a report shows or groups times for people in one place. India Standard Time (IST) is UTC plus 5 hours 30 minutes all year, so an order placed at 20:00 UTC is at 01:30 IST the next morning, on another calendar day. This lesson converts between UTC and IST in each engine, shows how text with an offset is read, and explains the limit that makes MySQL’s TIMESTAMP stop in the year 2038.

SQLite 3.49.1, running in your browser through sql.js 1.14.2, executes every example in this lesson; how PostgreSQL 18 and MySQL 8.4 handle zones is described from their manuals.

IST and the IANA zone names

Time zones are a matter of law, and their rules change, so software does not hard-code them: it reads the IANA Time Zone Database, which names each zone after a place. India’s zone is Asia/Kolkata. Its history in the database ends with IST, UTC+05:30, from 1945 onwards, after war-time years with an extra hour; India has no daylight saving time, so the offset is the same on every day of the year. Zones with daylight saving, such as Europe/London or America/New_York, change their offset twice a year, which is why a named zone is safer than a fixed offset whenever data from other countries is involved. PostgreSQL uses the IANA data for its zone names, and MySQL can load it into its time zone tables.

UTC and IST, side by side

SQLite knows no zone names and has no zone-aware type: a timestamp is text, and the modifier '+05:30' shifts it by 5 hours 30 minutes.

-- One moment on two clocks: UTC, as stored, and India Standard Time (UTC+05:30), as shown.
CREATE TABLE orders (id INTEGER, placed_utc TEXT);
INSERT INTO orders VALUES
  (1, '2026-03-31 12:00:00'), (2, '2026-03-31 18:29:59'),
  (3, '2026-03-31 18:30:00'), (4, '2026-03-31 20:00:00');

SELECT id,
       placed_utc,
       datetime(placed_utc, '+05:30') AS placed_ist,
       date(placed_utc)               AS utc_day,
       date(placed_utc, '+05:30')     AS ist_day
FROM orders;

┌────┬─────────────────────┬─────────────────────┬────────────┬────────────┐
│ id │     placed_utc      │     placed_ist      │  utc_day   │  ist_day   │
├────┼─────────────────────┼─────────────────────┼────────────┼────────────┤
│ 1  │ 2026-03-31 12:00:00 │ 2026-03-31 17:30:00 │ 2026-03-31 │ 2026-03-31 │
│ 2  │ 2026-03-31 18:29:59 │ 2026-03-31 23:59:59 │ 2026-03-31 │ 2026-03-31 │
│ 3  │ 2026-03-31 18:30:00 │ 2026-04-01 00:00:00 │ 2026-03-31 │ 2026-04-01 │
│ 4  │ 2026-03-31 20:00:00 │ 2026-04-01 01:30:00 │ 2026-03-31 │ 2026-04-01 │
└────┴─────────────────────┴─────────────────────┴────────────┴────────────┘

The first two orders fall on the same day in both zones. From 18:30 UTC onwards the IST clock has passed midnight, so orders 3 and 4 belong to the next day in India: grouped by utc_day, all four look like orders of the same day, while grouped by ist_day two of them move. Order 4 also moves into a new month and, in India, a new financial year.

SQLite also knows the modifier 'localtime', which converts UTC to the time zone of the computer running the query. It is handy on a laptop and unreliable on a server, whose zone is often UTC, and it gives different answers on different machines, so this lesson’s examples never use it: they name the offset.

Offsets inside the text

A timestamp written with its offset, such as 2026-04-01T01:30:00+05:30 in the RFC 3339 style, names one moment without ambiguity. SQLite reads such offsets and converts the time to UTC:

-- Time-values with an offset or a Z are converted to UTC. Without either, SQLite takes the text to be UTC already.
SELECT datetime('2026-04-01 01:30:00+05:30') AS with_offset,
       datetime('2026-03-31T20:00:00Z')      AS with_z,
       datetime('2026-04-01 01:30:00')       AS without_offset,
       datetime('2026-04-01 01:30:00+0530')  AS offset_without_colon;

┌─────────────────────┬─────────────────────┬─────────────────────┬──────────────────────┐
│     with_offset     │       with_z        │   without_offset    │ offset_without_colon │
├─────────────────────┼─────────────────────┼─────────────────────┼──────────────────────┤
│ 2026-03-31 20:00:00 │ 2026-03-31 20:00:00 │ 2026-04-01 01:30:00 │                      │
└─────────────────────┴─────────────────────┴─────────────────────┴──────────────────────┘

+05:30 and Z (for UTC) are understood, and both strings here name the same moment, 20:00 UTC. Text without an offset is taken to be UTC already, so an IST clock reading stored without its offset is silently 5 hours 30 minutes off. The offset must be written with a colon: +0530 is not accepted and gives NULL.

PostgreSQL reads offsets too, but only for the type timestamp with time zone. Its manual warns that a value of timestamp without time zone silently ignores any zone written in the input, so '2026-04-01 01:30:00+05:30' cast to timestamp keeps 01:30 and drops the offset.

Store UTC, convert at the edges

The safe pattern is to convert local input to UTC once, when it is stored, and to convert back only for display:

-- Store UTC, convert at the edges: an order entered at 01:30 IST is stored as 20:00 UTC the evening before.
CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_utc TEXT NOT NULL);
INSERT INTO orders (placed_utc) VALUES (datetime('2026-04-01 01:30:00', '-05:30'));

SELECT id,
       placed_utc,
       datetime(placed_utc, '+05:30') AS shown_in_ist
FROM orders;

┌────┬─────────────────────┬─────────────────────┐
│ id │     placed_utc      │    shown_in_ist     │
├────┼─────────────────────┼─────────────────────┤
│ 1  │ 2026-03-31 20:00:00 │ 2026-04-01 01:30:00 │
└────┴─────────────────────┴─────────────────────┘

The person entered 01:30 IST, the table holds 20:00 UTC on the evening before, and the report shows 01:30 IST again. Every stored row now uses the same clock, so sorting, differences and joins with data from other systems work without asking which zone each row was written in. A column that mixes UTC rows with IST rows cannot be repaired afterwards, because nothing in the text says which is which.

Instants and clock readings in each engine

PostgreSQL and MySQL each have one type for moments and one for clock readings, and they convert in different places:

In PostgreSQL a timestamptz value is stored as a UTC instant and printed in the session’s time zone: with SET TIME ZONE 'Asia/Kolkata', an instant stored as 12:00 UTC prints as 17:30 with the offset +05:30. AT TIME ZONE works in both directions. Applied to a timestamptz, it returns the clock reading in the named zone, a timestamp; applied to a timestamp, it reads the clock as a time in that zone and returns the instant, a timestamptz. Use full zone names such as 'Asia/Kolkata'. The manual notes that POSIX-style zone strings flip the sign, so a name such as 'UTC+5:30' means a zone behind UTC, not ahead of it.

MySQL converts TIMESTAMP values from the session’s time_zone to UTC when it stores them and back when it reads them, while DATETIME keeps whatever it was given. A connection whose zone differs from the one that wrote the data therefore reads different TIMESTAMP values, and identical DATETIME ones. The time_zone setting accepts offsets such as '+05:30' at once, but names such as 'Asia/Kolkata' only after the time zone tables have been loaded: until then, SET time_zone with a name fails with error 1298, and CONVERT_TZ() with a name returns NULL, as it does for any invalid argument.

Unix time and the year 2038

Unix time counts the seconds since the first moment of 1970 in UTC, so it names a moment with a single integer and no zone at all:

-- Unix time counts seconds from 1970-01-01 00:00:00 UTC, so it names a moment without needing a zone.
SELECT unixepoch('2026-03-31 20:00:00')             AS unix_seconds,
       datetime(1774987200, 'unixepoch')             AS as_utc,
       datetime(1774987200, 'unixepoch', '+05:30')   AS as_ist;

-- The largest value of a signed 32-bit integer, and one second later.
SELECT datetime(2147483647, 'unixepoch') AS last_32_bit_second,
       datetime(2147483648, 'unixepoch') AS one_second_later;

┌──────────────┬─────────────────────┬─────────────────────┐
│ unix_seconds │       as_utc        │       as_ist        │
├──────────────┼─────────────────────┼─────────────────────┤
│ 1774987200   │ 2026-03-31 20:00:00 │ 2026-04-01 01:30:00 │
└──────────────┴─────────────────────┴─────────────────────┘
┌─────────────────────┬─────────────────────┐
│ last_32_bit_second  │  one_second_later   │
├─────────────────────┼─────────────────────┤
│ 2038-01-19 03:14:07 │ 2038-01-19 03:14:08 │
└─────────────────────┴─────────────────────┘

unixepoch() turns text into seconds and the 'unixepoch' modifier turns seconds back into text, to which an offset can be added for display. The second query shows a famous limit. 2147483647, the largest signed 32-bit integer, is the last second that 32-bit Unix time can count, early on a January morning of 2038 in UTC. SQLite stores integers in 64 bits and simply goes on, but MySQL’s TIMESTAMP type stops exactly there: its range ends at 03:14:07 UTC on that day, and in strict mode an INSERT of a later moment fails. Expiry dates, loan schedules and certificates already reach past that point, so store far-future moments in DATETIME (in UTC, by your own rule) or as BIGINT seconds.

Your turn

-- One row per IST calendar day with at least one paid order:
-- ist_day (YYYY-MM-DD in IST) and orders (the number of paid orders that day).
SELECT date(placed_utc) AS ist_day,
       count(*)         AS orders
FROM orders
GROUP BY ist_day;

# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: moves orders from 18:30 UTC onwards to the next IST day
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_utc TEXT NOT NULL, status TEXT NOT NULL);
      INSERT INTO orders VALUES
        (1, '2026-03-31 12:00:00', 'paid'), (2, '2026-03-31 18:29:59', 'paid'), (3, '2026-03-31 18:30:00', 'paid'),
        (4, '2026-03-31 23:59:59', 'paid'), (5, '2026-04-01 06:00:00', 'paid');
    expected:
      columns: [ist_day, orders]
      rows:
        - ['2026-03-31', 2]
        - ['2026-04-01', 3]
  - name: skips cancelled orders and crosses the turn of the year
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_utc TEXT NOT NULL, status TEXT NOT NULL);
      INSERT INTO orders VALUES
        (1, '2026-12-31 19:00:00', 'paid'), (2, '2026-12-31 17:00:00', 'paid'), (3, '2026-12-31 20:00:00', 'cancelled'),
        (4, '2027-01-01 18:45:00', 'paid');
    expected:
      columns: [ist_day, orders]
      rows:
        - ['2026-12-31', 1]
        - ['2027-01-01', 1]
        - ['2027-01-02', 1]
  - name: leaves out a day whose only orders were cancelled
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_utc TEXT NOT NULL, status TEXT NOT NULL);
      INSERT INTO orders VALUES
        (1, '2026-06-10 10:00:00', 'cancelled'), (2, '2026-06-11 02:00:00', 'paid'), (3, '2026-06-10 20:00:00', 'paid');
    expected:
      columns: [ist_day, orders]
      rows:
        - ['2026-06-11', 2]

Interview questions

Fresher · What is the difference between timestamp and timestamptz in PostgreSQL?

timestamp (without time zone) is a clock reading: PostgreSQL stores the date and time exactly as given and ignores any offset written in the input, so it cannot tell whether 09:00 meant IST or UTC. timestamptz (with time zone) is an instant: input with an offset, or interpreted in the session’s time zone, is converted to UTC and stored as UTC, and on output it is shown in the session’s current zone, so the same row prints as 12:00+00 for one session and 17:30+05:30 for another without being changed. The name misleads, because no zone is stored, only the instant. Use timestamptz for events that happened at a moment, such as orders, payments and logins, and plain timestamp or date for local schedules, such as a shop that opens at 09:00 wherever it is.

Key takeaways

• Store moments in UTC, as timestamptz in PostgreSQL, as UTC text or Unix seconds in SQLite, and convert to a local zone only for input and display.
• IST is UTC+05:30 all year (Asia/Kolkata), so from 18:30 UTC onwards an event belongs to the next IST day.
• SQLite shifts with modifiers such as '+05:30', converts text with +05:30 or Z to UTC, treats text without an offset as UTC, and refuses offsets without a colon.
• PostgreSQL’s timestamp ignores offsets in its input; AT TIME ZONE converts in both directions; POSIX-style zone strings flip the sign.
• MySQL converts TIMESTAMP through the session time_zone and leaves DATETIME alone; named zones need the time zone tables.
• MySQL’s TIMESTAMP ends with the last second of signed 32-bit Unix time in the year 2038; use DATETIME or BIGINT seconds for later moments.