Query Data over Time

This tutorial continues from Get Started with Hydrolix. It queries the same web_logs table to see how traffic, errors, and response times change over one day. Along the way, it uses the ClickHouse functions that group rows by time. It takes about 15 minutes.

What you'll do⚓︎

Count requests per hour.
Zoom in on a busy hour.
Fill in empty periods.
Find when errors happened.
Track response times over the day.

Before you begin⚓︎

Complete Get Started with Hydrolix through its query steps, and skip the cleanup step. The commands here use the HDX_HOSTNAME and HDX_TOKEN variables from that tutorial.

The token from Get Started is valid for 24 hours. If a query returns an authentication error, get a new token as described in Get an API token.

Count requests per hour⚓︎

toStartOfHour() rounds each timestamp down to the start of its hour. Grouping by that value gives one row per hour.

Requests per Hour
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfHour(timestamp) AS hour, count() AS requests
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
    GROUP BY hour
    ORDER BY hour"

Requests per Hour: Result

2026-09-01 00:00:00  5
2026-09-01 01:00:00  6
2026-09-01 02:00:00  5
2026-09-01 03:00:00  4
2026-09-01 04:00:00  8
2026-09-01 05:00:00  11
2026-09-01 06:00:00  13
2026-09-01 07:00:00  12
2026-09-01 08:00:00  9
2026-09-01 09:00:00  9
2026-09-01 10:00:00  4
2026-09-01 11:00:00  7
2026-09-01 12:00:00  7
2026-09-01 13:00:00  12
2026-09-01 14:00:00  7
2026-09-01 15:00:00  4
2026-09-01 16:00:00  13
2026-09-01 17:00:00  10
2026-09-01 18:00:00  8
2026-09-01 19:00:00  13
2026-09-01 20:00:00  8
2026-09-01 21:00:00  11
2026-09-01 22:00:00  6
2026-09-01 23:00:00  8

The six hours after midnight are the quietest, with 39 requests in all. Sort by the count to find the busiest hours. Sorting by hour second keeps hours with the same count in time order.

Busiest Hours
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfHour(timestamp) AS hour, count() AS requests
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
    GROUP BY hour
    ORDER BY requests DESC, hour
    LIMIT 3"

Busiest Hours: Result
1 2 3	`2026-09-01 06:00:00 13 2026-09-01 16:00:00 13 2026-09-01 19:00:00 13`

Other functions round to other units, such as toStartOfMinute() and toStartOfDay(). See Dates and Times in the ClickHouse documentation.

Zoom in on a busy hour⚓︎

toStartOfInterval() rounds to any interval, such as 10 minutes. Narrow the time filter to the 06:00 hour and count requests in 10-minute periods.

Requests per 10 Minutes
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfInterval(timestamp, INTERVAL 10 MINUTE) AS period, count() AS requests
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 06:00:00' AND timestamp < '2026-09-01 07:00:00'
    GROUP BY period
    ORDER BY period"

Requests per 10 Minutes: Result
1 2 3 4	`2026-09-01 06:20:00 2 2026-09-01 06:30:00 2 2026-09-01 06:40:00 5 2026-09-01 06:50:00 4`

The result starts at 06:20. No requests arrived between 06:00 and 06:20, and a period with no rows doesn't appear in the result.

Fill in empty periods⚓︎

A line chart connects each point to the next. A period with no rows has no point, so the line passes over it and the drop to zero never shows. A chart of the 10-minute counts in the previous step would start at 06:20, with no sign of the 20 quiet minutes before it.

To show empty periods as zero, add WITH FILL to the ORDER BY clause. FROM and TO set the range to fill, and STEP matches the period length. The range includes FROM and stops before TO.

Requests per 10 Minutes with Empty Periods
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfInterval(timestamp, INTERVAL 10 MINUTE) AS period, count() AS requests
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 06:00:00' AND timestamp < '2026-09-01 07:00:00'
    GROUP BY period
    ORDER BY period WITH FILL
      FROM toDateTime('2026-09-01 06:00:00')
      TO toDateTime('2026-09-01 07:00:00')
      STEP INTERVAL 10 MINUTE"

Requests per 10 Minutes with Empty Periods: Result
1 2 3 4 5 6	`2026-09-01 06:00:00 0 2026-09-01 06:10:00 0 2026-09-01 06:20:00 2 2026-09-01 06:30:00 2 2026-09-01 06:40:00 5 2026-09-01 06:50:00 4`

For more fill options, see ORDER BY Modifier WITH FILL in the ClickHouse documentation.

Find when errors happened⚓︎

Get Started found four server errors, all from /checkout. List them in time order.

Server Errors in Time Order
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT timestamp, path, status
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
      AND status >= 500
    ORDER BY timestamp"

Server Errors in Time Order: Result
1 2 3 4	`2026-09-01 03:55:34 /checkout 500 2026-09-01 06:37:37 /checkout 500 2026-09-01 12:23:54 /checkout 500 2026-09-01 20:34:24 /checkout 500`

To see how far apart they were, compare each error with the one before it. lagInFrame() is a window function that reads a value from the previous row. dateDiff('minute', ...) counts the minute boundaries between the two timestamps, which matches elapsed minutes closely for gaps this long.

Minutes Between Server Errors
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT timestamp,
           dateDiff('minute', lagInFrame(toNullable(timestamp)) OVER (ORDER BY timestamp), timestamp) AS minutes_since_previous
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
      AND status >= 500
    ORDER BY timestamp"

Minutes Between Server Errors: Result
1 2 3 4	`2026-09-01 03:55:34 \N 2026-09-01 06:37:37 162 2026-09-01 12:23:54 346 2026-09-01 20:34:24 491`

The first error has no error before it, so lagInFrame() has nothing to read and returns null, shown as \N. That's why the query wraps timestamp in toNullable(). Without it, lagInFrame() falls back to the default timestamp, 1970-01-01 00:00:00, and the first gap comes out at more than 50 years. After the first error, each gap is longer than the one before. To learn more about functions like lagInFrame(), see Window Functions.

Track response times over the day⚓︎

quantiles() calculates several percentiles at once and returns them as an array. On large tables, it samples the data, so the values are approximate. Group by 6-hour periods to compare the median and the 95th percentile across the day. arrayMap() rounds each value in the array.

Response Time Percentiles by Period
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfInterval(timestamp, INTERVAL 6 HOUR) AS period,
           arrayMap(x -> round(x, 1), quantiles(0.5, 0.95)(response_time_ms)) AS p50_p95_ms
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
    GROUP BY period
    ORDER BY period"

Response Time Percentiles by Period: Result
1 2 3 4	`2026-09-01 00:00:00 [49.9,191.6] 2026-09-01 06:00:00 [35.8,134.4] 2026-09-01 12:00:00 [42.5,97.9] 2026-09-01 18:00:00 [45.1,116.2]`

The median stays between about 36 and 50 ms all day. The 95th percentile is highest from 00:00 to 06:00, the period with the fewest requests, where a few slow requests count for more. See quantiles.

More to try⚓︎

Local timeMoving average

Timestamps in this table are in UTC. To group by hours in another time zone, pass the zone to toStartOfHour().

Requests per Hour in New York Time
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT toStartOfHour(timestamp, 'America/New_York') AS hour, count() AS requests
    FROM getting_started.web_logs
    WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
    GROUP BY hour
    ORDER BY hour
    LIMIT 3"

Requests per Hour in New York Time: Result
1 2 3	`2026-08-31 20:00:00 5 2026-08-31 21:00:00 6 2026-08-31 22:00:00 5`

Midnight UTC is 20:00 the evening before in New York, so the counts match the first hours of the UTC result.

A window function can also smooth the hourly counts. This query averages each hour with the two hours before it.

Three-Hour Moving Average
curl -s -G "https://${HDX_HOSTNAME}/query" \
  -H "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=
    SELECT hour, requests,
           round(avg(requests) OVER (ORDER BY hour ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3h
    FROM (
      SELECT toStartOfHour(timestamp) AS hour, count() AS requests
      FROM getting_started.web_logs
      WHERE timestamp >= '2026-09-01 00:00:00' AND timestamp < '2026-09-02 00:00:00'
      GROUP BY hour
    )
    ORDER BY hour
    LIMIT 6"

Three-Hour Moving Average: Result
1 2 3 4 5 6	`2026-09-01 00:00:00 5 5 2026-09-01 01:00:00 6 5.5 2026-09-01 02:00:00 5 5.3 2026-09-01 03:00:00 4 5 2026-09-01 04:00:00 8 5.7 2026-09-01 05:00:00 11 7.7`

The first two hours average fewer than three hours, because there are no earlier hours.

Clean up⚓︎

To remove the data, delete the getting_started project as described in Clean up in Get Started.

Next steps⚓︎

Look up other time functions. See Dates and Times in the ClickHouse documentation.
Learn how Hydrolix runs SQL. See ClickHouse Considerations.