Как посчитать Hour-of-Day patterns в SQL

Зачем Hour-of-Day

Daily peak usage hours — drive: support staffing, marketing send time, capacity planning, deploy windows. Restaurant lunch peak. E-com evening shopping. SaaS B2B morning ramp.

EXTRACT(HOUR)

EXTRACT(HOUR FROM '2026-05-13 14:30:00'::TIMESTAMP)  -- returns 14

Базовый расчёт

SELECT
    EXTRACT(HOUR FROM event_time) AS hour,
    COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS unique_users
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

Peak hours

WITH hourly AS (
    SELECT
        EXTRACT(HOUR FROM event_time) AS hour,
        COUNT(*) AS events
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY 1
)
SELECT
    hour,
    events,
    events::NUMERIC / SUM(events) OVER () * 100 AS share_pct,
    RANK() OVER (ORDER BY events DESC) AS rank
FROM hourly
ORDER BY hour;

Top 3 hours obvious. Plot на hourly chart — bimodal distribution typical.

Time-zone aware

Events stored в UTC. User в Moscow (UTC+3) opens app 9am local = 6am UTC:

SELECT
    EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/Moscow') AS hour_local,
    COUNT(*) AS events
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

Multiple time zones — per-user tz:

SELECT
    EXTRACT(HOUR FROM e.event_time AT TIME ZONE u.tz) AS hour_local,
    COUNT(*) AS events
FROM events e
JOIN users u USING (user_id)
WHERE e.event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

DOW × HOD heatmap

SELECT
    EXTRACT(DOW FROM event_time) AS dow,
    EXTRACT(HOUR FROM event_time) AS hour,
    COUNT(*) AS events
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY 1, 2
ORDER BY 1, 2;

Plot — heatmap shows when users active.

Best send time для emails

WITH historic_opens AS (
    SELECT
        EXTRACT(HOUR FROM open_time) AS hour,
        EXTRACT(DOW FROM open_time) AS dow,
        COUNT(*) AS opens
    FROM email_events
    WHERE event_type = 'opened'
      AND open_time >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY 1, 2
)
SELECT
    dow,
    hour,
    opens,
    RANK() OVER (PARTITION BY dow ORDER BY opens DESC) AS rank_within_dow
FROM historic_opens
WHERE RANK() OVER (PARTITION BY dow ORDER BY opens DESC) <= 3
ORDER BY dow, rank_within_dow;

Top-3 hours per DOW — when send email.

Latency hour pattern

SELECT
    EXTRACT(HOUR FROM TIMESTAMP) AS hour,
    COUNT(*) AS requests,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_latency
FROM api_logs
WHERE TIMESTAMP >= NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1;

Peak hour latency higher (load).

Частые ошибки

Ошибка 1. Time-zone ignored. Events UTC, users глобал. Plot UTC hour useless. Convert.

Ошибка 2. Stack ranks per dom. Hours 0-23 → bar chart looks weird. Use heatmap format.

Ошибка 3. Single day sample. Day of week matters. Use 7+ days minimum.

Ошибка 4. DST changes. 2 AM doesn't exist twice yearly. Handle DST transitions.

Ошибка 5. Aggregate vs per-user pattern. Aggregate peaks 6pm. Each user has own pattern. Two different stories.

Связанные темы

• Как посчитать day of week analysis в SQL
• Как посчитать seasonality в SQL
• Как посчитать API latency в SQL
• Window functions advanced

FAQ

Time-zone handling?

Store UTC. Convert при reporting. AT TIME ZONE 'timezone' в PostgreSQL.

Best hours для push?

Sender depend industry. Banking: 10am-2pm. Consumer apps: 6-9pm. Always A/B test.

DST handling?

Use IANA tz names ('Europe/Moscow'). They handle DST automatically.

Hour granularity ok?

For most analysis yes. For real-time SLO — 5-min buckets.

Peak hour for support staffing?

DOW × HOD heatmap. Staff matches peak.