Extract the hour-of-day (0 through 23) from a timestamp.
HOUR(timestamp)## Overview Extracts the hour-of-day component from a TIMESTAMP (or TIME) value, returned as an INTEGER between 0 and 23. Equivalent to EXTRACT(HOUR FROM input). Use HOUR for time-of-day distribution analysis, shift-based filtering, and any aggregation that groups by hour. ## Behavior - Returns an INTEGER in the range 0 through 23. - Returns NULL if the argument is NULL. - Accepts TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIME inputs. - 24-hour clock (no AM/PM); midnight is 0, 1 PM is 13. ## Timezone handling - TIMESTAMP WITH TIME ZONE values are converted to the session zone before the hour is taken. - To read the hour in a specific zone regardless of session, use (ts AT TIME ZONE 'zone_name') first. - DST transitions cause hours to repeat (fall back) or be skipped (spring forward) within a single local day when TIMESTAMP WITH TIME ZONE is interpreted locally. ## Compatibility - Equivalent to EXTRACT(HOUR FROM source).
| Name | Type | Description |
|---|---|---|
timestamp | Specifies the timestamp or time value from which to extract the hour component. |
-- Afternoon hour returns 14
SELECT HOUR(TIMESTAMP '2025-03-15 14:30:00') AS hr;-- Midnight returns 0
SELECT HOUR(TIMESTAMP '2025-03-15 00:00:00') AS hr;-- Events by hour of day across all days
SELECT HOUR(event_ts) AS hr, COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY hr
ORDER BY hr;-- Filter evening requests
SELECT request_id FROM telemetry.web.requests WHERE HOUR(event_ts) BETWEEN 18 AND 22;-- Hour distribution of user signups in a specific zone
SELECT HOUR(signup_ts AT TIME ZONE 'America/New_York') AS hr_et, COUNT(*) AS users
FROM analytics.users.engagement
GROUP BY hr_et
ORDER BY hr_et;