select data where time in datetime-column is in range (SQL, Clickhouse)

Question

I have a query in Clickhouse which counts the rows in 15-minute interval:

SELECT 
    toStartOfInterval(table.timestamp, INTERVAL 15 minute) AS interval_start,
    COUNT(*),
    station,
    ticket_name
FROM table
where date between '2018-01-01' and '2022-12-31'
GROUP BY interval_start, station, ticket_name
order by interval_start

timestamp has the following format: 2018-01-01T05:00:00

I want to add another condition to get all the values where time is in range between 05:00:00 and 10:00:00. How do I make this?

Answer 1

You can add an additional condition in the WHERE clause using the EXTRACT function to extract the hour component of the timestamp and check if it's between 5 and 10. Here's a version of your query:

SELECT 
    toStartOfInterval(table.timestamp, INTERVAL 15 minute) AS interval_start,
    COUNT(*),
    station,
    ticket_name
FROM table
WHERE date BETWEEN '2018-01-01' AND '2022-12-31'
    AND EXTRACT(hour FROM table.timestamp) BETWEEN 5 AND 10
GROUP BY interval_start, station, ticket_name
ORDER BY interval_start

Or add:

AND toHour(table.timestamp) >= 5 AND toHour(table.timestamp) <= 10