SQL TIMEZONE_HOUR

What is SQL TIMEZONE_HOUR?

TIMEZONE_HOUR returns the hour portion of the time-zone offset in a timestamp or interval value.

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 TIMEZONE_HOUR: PostgreSQL, Oracle, Snowflake, IBM Db2, Teradata, Google BigQuery, Firebird; MySQL and SQL Server require workarounds (no direct support).

SQL TIMEZONE_HOUR Full Explanation

TIMEZONE_HOUR is a datetime field identifier defined by the SQL standard. It is not a standalone function; instead, it is supplied to the EXTRACT (or equivalent) function to obtain the signed hour component of a timestamp's UTC offset. For example, a timestamp with time zone '+05:30' has a TIMEZONE_HOUR of 5, while '-08:00' has ‑8.Returned type is an integer in the range ‑14 to +14, matching the valid global time-zone offset hours. When the underlying value has no time-zone information (plain TIMESTAMP), the result is implementation-dependent or NULL, so always use a time-zone-aware datatype. Negative offsets include the sign (e.g., -8).Supported expressions:- EXTRACT(TIMEZONE_HOUR FROM )- DATE_PART('timezone_hour', ) in PostgreSQLBecause TIMEZONE_HOUR is part of the SQL:1999 standard, most modern databases expose it, though syntax can vary.

SQL TIMEZONE_HOUR Syntax

-- Standard SQL
EXTRACT(TIMEZONE_HOUR FROM <timestamp_with_time_zone>);

-- PostgreSQL
SELECT DATE_PART('timezone_hour', <timestamptz>);

-- Oracle
SELECT EXTRACT(TIMEZONE_HOUR FROM <timestamp_tz>) FROM dual;

SQL TIMEZONE_HOUR Parameters

Example Queries Using SQL TIMEZONE_HOUR

-- 1. Standard SQL example
SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2024-03-15 12:00:00+05:30');

-- 2. PostgreSQL example using NOW()
SELECT DATE_PART('timezone_hour', NOW());

-- 3. Oracle example comparing two values
SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2024-01-01 08:00:00-08:00') AS tz_hour_pst,
       EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2024-01-01 12:00:00+01:00') AS tz_hour_cet
FROM dual;

Expected Output Using SQL TIMEZONE_HOUR

Returns 5
Returns the server session's time-zone offset hour (e.g., -4)
Returns -8 for tz_hour_pst and 1 for tz_hour_cet

Use Cases with SQL TIMEZONE_HOUR

Displaying or logging the raw hour portion of a user's time-zone for analytics
Normalizing data by separating hour and minute components before storing offsets
Validating that stored timestamps fall within allowed time-zone ranges
Building reports that group data by time-zone hour buckets

Common Mistakes with SQL TIMEZONE_HOUR

Calling TIMEZONE_HOUR as if it were a function: TIMEZONE_HOUR(timestamp) is invalid; use EXTRACT.
Extracting from a plain TIMESTAMP without time zone, which yields NULL or zero.
Forgetting that negative offsets keep the sign, leading to mis-grouping when aggregating.
Assuming every database uses the keyword spelling; some require string literals like 'timezone_hour'.

First Introduced In

SQL:1999

Frequently Asked Questions

What does TIMEZONE_HOUR stand for?

It is a datetime field identifier used with EXTRACT to obtain the signed hour component of a timestamp's UTC offset.

Can I use TIMEZONE_HOUR without EXTRACT?

No. TIMEZONE_HOUR is not a standalone function. Supply it to EXTRACT or DATE_PART depending on the database.

What range of values can TIMEZONE_HOUR return?

Values range from -14 to +14, matching the extreme legal time-zone offsets worldwide.

How is TIMEZONE_HOUR different from TIMEZONE_MINUTE?

TIMEZONE_HOUR gives the hour part of the offset, while TIMEZONE_MINUTE returns the minute part (0 or 30/45 in most zones).