SQL Extract Hour

Returns the hour of the day from a timestamp — 0 through 23, in 24-hour format.

What & Why

EXTRACT(HOUR FROM timestamp_column) returns the hour, 0 through 23. This one specifically requires a TIMESTAMP — a plain DATE has no time-of-day component to extract an hour from at all.

See How It Works

BUSINESS QUESTION

Growth wants event volume by hour of day.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  EXTRACT(HOUR FROM created_at)::int AS event_hour,
  COUNT(*) AS events
FROM growth.events
GROUP BY event_hour
ORDER BY event_hour;

RESULT — exact output from the displayed Queryflo rows

event_hour	events
9	1
11	1
14	1
16	1

The displayed UTC event timestamps occupy four distinct hours.

Now You Try

Practice this concept

Growth wants event volume by hour of day.

Available schema

growth

Prefix tables with growth.table_name.

event_hourevents

growth.events

Column	Type
id	bigint
user_id	integer
event_name	text
created_at	timestamp with time zone
properties	jsonb
deprecated_metric	text

query.sql

Intermediate business practice