SQL HOUR

What is SQL HOUR?

Returns the hour (0-23) from a TIME, DATE, or TIMESTAMP expression.

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 HOUR: PostgreSQL, MySQL, SQL Server, Oracle, SQLite (via strftime('%H', expr)), Snowflake, BigQuery, Redshift

SQL HOUR Full Explanation

HOUR is a date-time extraction keyword or function that isolates the hour portion of a temporal value. In ANSI/Standard SQL it appears inside the EXTRACT clause (EXTRACT(HOUR FROM expr)). MySQL exposes it as a standalone scalar function (HOUR(expr)), SQL Server and Oracle use DATEPART(hour, expr), while PostgreSQL offers both EXTRACT and the date_part('hour', expr) helper. The result is an integer between 0 and 23. If the input is NULL, the result is NULL. Fractional seconds are ignored. For timestamp values with time zone, most engines first convert to the session time zone before extracting. HOUR is immutable and deterministic and can be used in SELECT lists, WHERE filters, GROUP BY, ORDER BY, window functions, and index definitions. Because it returns only the hour, it is often paired with other date parts or wrapped in DATE_TRUNC when higher precision is unnecessary.

SQL HOUR Syntax

-- Standard SQL
EXTRACT(HOUR FROM <datetime_expression>);

-- MySQL
HOUR(<datetime_expression>);

-- SQL Server / Oracle
DATEPART(hour, <datetime_expression>);

SQL HOUR Parameters

DATETIME (TIMESTAMP) - TIME Temporal expression from which to extract the hour

Example Queries Using SQL HOUR

-- PostgreSQL
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-08-25 14:35:22') AS hour_component;

-- MySQL
SELECT HOUR('2023-08-25 14:35:22') AS hour_component;

-- SQL Server
SELECT DATEPART(hour, '2023-08-25 14:35:22') AS hour_component;

Expected Output Using SQL HOUR

Each query returns the integer 14

Use Cases with SQL HOUR

Bucketing events by hour in time-series analysis
Filtering rows that occurred during specific hours (e.g., business hours 9-17)
Building hourly aggregations for dashboards
Auditing peak traffic periods by extracting the hour and grouping

Common Mistakes with SQL HOUR

Expecting 1-12 instead of 0-23 (HOUR always returns 24-hour format)
Forgetting to cast string literals to a temporal data type before extraction
Ignoring session time zone settings when working with TIMESTAMP WITH TIME ZONE
Using HOUR on a DATE value in engines that do not implicitly cast DATE to TIMESTAMP

First Introduced In

SQL:1999

Frequently Asked Questions

What is the range of values returned by HOUR?

0 through 23 in 24-hour format.

Is HOUR deterministic?

Yes – given the same input and session time zone, it always returns the same result.

Can I use HOUR on a DATE column?

Only if the database implicitly casts DATE to TIMESTAMP or if you cast it manually; otherwise you will get an error.

How do I extract multiple date parts at once?

Invoke EXTRACT separately for each part or use DATE_TRUNC to zero out smaller units.