Part 3: Working with Time

References: - ClickHouse date and time functions - ClickHouse formatDateTime

One important aspect of the telemetry we receive is the time the events occurred. You were introduced to this earlier with the _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 filter. There are two different columns for this: _time_observed and _time_ingested. For this exercise, just use observed times.

Time values are stored as Unix epoch values, but the interface converts them behind the scenes to display human readable time in your time zone. You can use an online epoch converter to get the Unix value for a time. You can also click on the time field in a result to copy that value to the clipboard.

That can be fiddly though, and when investigating we often want to look a little further back for context. With Unix epoch time you can add to or subtract from the copied value. Use a - or + followed by the number of milliseconds you want to shift by:

1000 * 60                = 1 minute (1000 milliseconds * 60 seconds)
1000 * 60 * 60           = 1 hour
1000 * 60 * 60 * 24      = 1 day
1000 * 60 * 60 * 24 * 2  = 2 days

3a. Unix epoch time

Write a query that shows all columns from the endpoint_process table, modifying the _time_observed >= toUnixTimestamp(subtractHours(now(), 1)) * 1000 line to limit the search to 12 hours and the oldest 50 results. Sort by oldest to newest. Is the first record 12 hours before the current time?

Answer

SELECT *
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 12)) * 1000
ORDER BY _time_observed asc
LIMIT 50

Change the time limit to 1 hour and re-run the query, sorting oldest to newest. Click on the _time_observed column to copy the epoch value to the clipboard.

Answer

SELECT *
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 1)) * 1000
ORDER BY _time_observed asc
LIMIT 50

Copy the oldest epoch value from `_time_observed` to use in the next query.

Now modify the query above, removing the _time_observed >= toUnixTimestamp(subtractHours(now(), 1)) * 1000 line, so that it returns all process records from the 5 minutes before the value you copied, in ascending order of time. Is the first record in this output approximately 5 minutes before the oldest record from the previous query?

Hint - 5 minutes

There are 300000 milliseconds in 5 minutes.

Answer

SELECT *
FROM endpoint_process
WHERE
  _time_observed >= 1773400919205 - 300000 -- replace this epoch value with the one you copied
ORDER BY _time_observed asc
LIMIT 50

3b. Typecasting time

For certain operations, and to force time values into a human friendly form for easy timezone conversion, use toDateTime. This converts the Unix epoch value into a date time data type.

The structure is toDateTime(_time_observed / 1000, 'Continent/City') AS <meaningful_name>, and you do the conversion in the SELECT statement. Not specifying a timezone shows the equivalent time in UTC. The / 1000 matters, because epoch times here are in millisecond precision whereas toDateTime works in seconds.

Efficiency: This is a more human friendly way of expressing time constraints, but it is less efficient than filtering on the raw epoch value with toUnixTimestamp(subtractHours(now(), 1)) * 1000. You also need to keep track of which timezone you are converting to, against the timezone the interface is displaying.

Write a query that converts the time to UTC in the SELECT clause, for the 50 most recent entries in the endpoint_process table, using an AS statement to label the column as UTC time. The query should show the converted time, _hostname, name and parent_name. Keep using toUnixTimestamp(subtractHours(now(), <hours to subtract>)) * 1000 to limit the query so it runs efficiently. Notice that the formatting of the converted column differs from the normal _time_observed column.

Answer

SELECT
  toDateTime(_time_observed / 1000, 'UTC') AS UTCTime,
  _hostname,
  name,
  parent_name
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 1)) * 1000
ORDER BY _time_observed desc
LIMIT 50

Now modify the query so it displays the time in your own timezone. Does the most recent record match your current time?

Answer

SELECT
  toDateTime(_time_observed / 1000, 'Europe/London') AS MyTime, -- use your own timezone here
  _hostname,
  name,
  parent_name
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 1)) * 1000
ORDER BY _time_observed desc
LIMIT 50

3c. Working with dateTime

Once we have a date time data type, we can use a more human friendly syntax to specify which time ranges to look at in the WHERE clause. The format is YYYY-MM-DD HH:MM:SS on a 24 hour clock:

MyTime >= '2026-04-15 09:30:00'

Referring to your new column: ClickHouse lets you use an alias defined in the SELECT clause inside the WHERE clause, so once you have written AS MyTime you can filter on MyTime directly rather than repeating the whole toDateTime(...) expression.

Remove the toUnixTimestamp(subtractHours(now(), 1)) * 1000 line from the previous query and use the date time syntax instead, to limit the results to approximately the previous 3 hours. Order oldest to newest, using your own timezone. Are the first entries approximately 3 hours before your current time?

Answer

SELECT
  toDateTime(_time_observed / 1000, 'Europe/London') AS MyTime, -- use your own timezone here
  _hostname,
  name,
  parent_name
FROM endpoint_process
WHERE
  MyTime >= '2026-04-15 09:00:00' -- replace with a time approximately 3 hours ago
ORDER BY MyTime asc
LIMIT 50

Your times will differ from the example, so check the output against the clock rather than against a fixed value.

Adjust the query so it orders by newest first, and add a second condition so that the newest results are from an hour ago. Are the results now an hour behind your current time?

Answer

SELECT
  toDateTime(_time_observed / 1000, 'Europe/London') AS MyTime, -- use your own timezone here
  _hostname,
  name,
  parent_name
FROM endpoint_process
WHERE
  MyTime >= '2026-04-15 09:00:00' -- approximately 3 hours ago
  AND MyTime <= '2026-04-15 11:00:00' -- approximately 1 hour ago
ORDER BY MyTime desc
LIMIT 50

Write a query that returns _time_observed, _hostname, name, parent_name and command from the endpoint_process table, limited to 50 results from the past 3 days, typecasting _time_observed to a date time and then showing only the date the event occurred. No ordering is required. Do not use toDate for this: use the formatting function instead.

Hint - which function

`formatDateTime` takes a date time value and a format string, and returns just the parts you ask for. The format specifiers are listed in the ClickHouse documentation linked at the top of this page.

Answer

SELECT
  formatDateTime(toDateTime(_time_observed / 1000, 'Europe/London'), '%D') AS EventDate,
  _hostname,
  name,
  parent_name,
  command
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
LIMIT 50

`%D` returns the date as `MM/DD/YY`. Any date-only format is acceptable here, and the next question pins it to a specific one.

Modify the query above so the date is output in this format: 2026-04-15.

Hint - format specifier

A single specifier covers the whole ISO date, so you do not need to combine `%Y`, `%m` and `%d` yourself.

Answer

SELECT
  formatDateTime(toDateTime(_time_observed / 1000, 'Europe/London'), '%F') AS EventDate,
  _hostname,
  name,
  parent_name,
  command
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
LIMIT 50

Congratulations, you have completed the beginner level Hunt Lab questions. You can now structure queries, filter them, work with strings, and handle time in both epoch and human readable form.

Now for the intermediate level. From here on you will be given fewer clues, and you will often need to work out the columns you need from the shape of the expected result rather than from the question.

Great! Now you can move onto Part 4 of the Hunt Lab training.