SQL ZONE

What is the SQL ZONE keyword?

ZONE is a reserved keyword used in date-time data types and the AT TIME ZONE clause to specify or convert time zones.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ZONE: PostgreSQL, Oracle, SQL Server (AT TIME ZONE), Snowflake, BigQuery, DuckDB. MySQL and SQLite support time-zone functions but not the exact AT TIME ZONE syntax.

SQL ZONE Full Explanation

ZONE appears in two main contexts:1. Data type declaration – TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE store an absolute moment in time by attaching a time-zone offset to the value.2. AT TIME ZONE clause – converts a timestamp to a different time zone or interprets a timestamp without zone information as belonging to a given zone.When used in a column definition, ZONE indicates that the column will always keep track of the offset from UTC. When used in a query expression, ZONE triggers a run-time conversion that adjusts the underlying epoch value and returns either a timestamp with zone or without zone, depending on the dialect.Caveats:- ZONE does not by itself set the session time zone. Use SET TIME ZONE for that.- Literals such as 'UTC', 'America/Los_Angeles', or numeric offsets like '+02' must be single-quoted.- Daylight-saving rules follow the IANA time-zone database, so historical values can shift if the database is upgraded.

SQL ZONE Syntax

-- Column or variable definition
TIMESTAMP WITH TIME ZONE
TIME WITH TIME ZONE

-- Conversion in queries
expression AT TIME ZONE zone_literal

SQL ZONE Parameters

zone_literal (text) - An IANA zone name (e.g., 'UTC', 'Europe/Berlin') or a numeric offset (e.g., '+05|||30').

Example Queries Using SQL ZONE

-- 1. Store creation time with zone awareness
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Interpret a naive timestamp as US Pacific time
SELECT '2024-06-01 10:00'::timestamp AT TIME ZONE 'America/Los_Angeles';

-- 3. Convert the stored UTC time to local Berlin time
SELECT created_at AT TIME ZONE 'Europe/Berlin' AS created_berlin
FROM   orders;

Expected Output Using SQL ZONE

Table created; created_at will always keep its offset.
Returns 2024-06-01 10:00-07.
Each returned timestamp is shifted from UTC to Central European Time, including DST when applicable.

Use Cases with SQL ZONE

Persisting audit trails that must be timezone-safe.
Displaying local times in user dashboards.
Converting imported CSV data that lacks zone info into UTC.
Scheduling jobs relative to a specific region.

Common Mistakes with SQL ZONE

Forgetting to quote the zone name: AT TIME ZONE America/LA (error).
Assuming WITH TIME ZONE stores the original text; it stores a normalized UTC value plus offset.
Mixing session time zone with column time zone definition.
Expecting AT TIME ZONE to change data in the table; it only changes the query result.

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between WITH TIME ZONE and WITHOUT TIME ZONE?

WITH TIME ZONE stores the absolute moment (UTC plus offset). WITHOUT TIME ZONE stores only the local wall clock value with no zone context.

How do I set my session time zone?

Use SET TIME ZONE 'America/New_York'; This affects how TIMESTAMP WITHOUT TIME ZONE values are interpreted and displayed.

Can I store offsets instead of full zone names?

Yes. Numeric offsets like '+05:30' are valid zone literals, but they lack daylight-saving context.

Is ZONE case-sensitive?

Keyword recognition is case-insensitive, but zone literals are case-sensitive in some systems (e.g., 'UTC' vs 'utc').