Convert a Timestamp to a Different Time Zone

Shift a stored UTC timestamp to a local time zone for display using engine-specific conversion functions.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Convert a UTC event timestamp to UTC+5:30 (India Standard Time)

Both events are stored in UTC. Adding 5 hours 30 minutes (IST offset) shifts 14:00 UTC to 19:30 IST and 18:30 UTC to midnight (00:00 the next day). PostgreSQL applies AT TIME ZONE 'UTC' first to declare the stored value as UTC, then AT TIME ZONE 'Asia/Kolkata' to convert. SQL Server requires the Windows time zone name 'India Standard Time' rather than an IANA name.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), created_at DATETIME);

INSERT INTO
  events
VALUES
  (1, 'Launch', '2026-03-15 14:00:00'),
  (2, 'Review', '2026-03-15 18:30:00');

Validated query

SQL

SELECT
  id,
  name,
  created_at AS utc_time,
  CONVERT_TZ (created_at, '+00:00', '+05:30') AS ist_time
FROM
  events
ORDER BY
  id;

Expected result

id	name	utc_time	ist_time
1	Launch	2026-03-15 14:00:00	2026-03-15 19:30:00
2	Review	2026-03-15 18:30:00	2026-03-16 00:00:00

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), created_at DATETIME);

INSERT INTO
  events
VALUES
  (1, 'Launch', '2026-03-15 14:00:00'),
  (2, 'Review', '2026-03-15 18:30:00');

Validated query

SQL

SELECT
  id,
  name,
  created_at AS utc_time,
  CAST(
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' AS DATETIME
  ) AS ist_time
FROM
  events
ORDER BY
  id;

Expected result

id	name	utc_time	ist_time
1	Launch	2026-03-15T14:00:00.000	2026-03-15T19:30:00.000
2	Review	2026-03-15T18:30:00.000	2026-03-16T00:00:00.000

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), created_at TIMESTAMP);

INSERT INTO
  events
VALUES
  (1, 'Launch', '2026-03-15 14:00:00'),
  (2, 'Review', '2026-03-15 18:30:00');

Validated query

SQL

SELECT
  id,
  name,
  created_at AS utc_time,
  (
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata'
  ) AS ist_time
FROM
  events
ORDER BY
  id;

Expected result

id	name	utc_time	ist_time
1	Launch	2026-03-15T14:00:00.000Z	2026-03-15T19:30:00.000Z
2	Review	2026-03-15T18:30:00.000Z	2026-03-16T00:00:00.000Z

MySQL/MariaDB use CONVERT_TZ with offset strings. SQL Server uses AT TIME ZONE with Windows zone names. PostgreSQL uses AT TIME ZONE with IANA zone names. Timestamp formatting varies by driver.

Useful when

Where this command helps.

displaying created_at timestamps in a user's local time zone
converting event times stored in UTC for a region-specific report

Explanation

What the command is doing.

Timestamps are typically stored in UTC and converted to a local time zone at query time. Each engine provides a different mechanism. PostgreSQL uses the AT TIME ZONE operator, which returns a TIMESTAMP WITH TIME ZONE. SQL Server also supports AT TIME ZONE (2016+) and returns a DATETIMEOFFSET. MySQL and MariaDB use the CONVERT_TZ(ts, from_tz, to_tz) function; named time zones (e.g., 'America/New_York') require the mysql.time_zone tables to be populated, but UTC offset strings (e.g., '+05:30') work without them. SQLite stores timestamps as text or integers and has no built-in time zone function — conversions are typically handled in application code.

Convert a Timestamp to a Different Time Zone

Convert a UTC event timestamp to UTC+5:30 (India Standard Time)

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.