---
title: "Querying DateTimes in ClickHouse"
description: "ClickHouse provides specialized functions to filter, bucket, and compute durations on native DateTime columns without requiring external processing tools. Using the New York City taxi dataset imported into a MergeTree table, queries bucket pickup timestamps into hour and 15-minute intervals using toStartOfHour and toStartOfFifteenMinutes while casting values to the Time type to group across dates. The dateDiff function calculates journey durations in minutes, which enables trip speed calculations across time windows and powers inline ASCII visualizations generated by the bar function. By combining toDayOfWeek with conditional aggregations like countIf and the lag window function, queries uncover distinct morning surge trajectories between weekdays and weekends. The analysis demonstrates that weekday trip volumes peak around 8:45 AM alongside dropping speeds, whereas weekend volumes increase steadily across the morning."
---

# Querying DateTimes in ClickHouse

[Clickhouse](https://yomu.fyi/company/clickhouse) · Mark Needham · Mar 20, 2026

**Type:** Tutorial

## Summary

ClickHouse provides specialized functions to filter, bucket, and compute durations on native DateTime columns without requiring external processing tools. Using the New York City taxi dataset imported into a MergeTree table, queries bucket pickup timestamps into hour and 15-minute intervals using toStartOfHour and toStartOfFifteenMinutes while casting values to the Time type to group across dates. The dateDiff function calculates journey durations in minutes, which enables trip speed calculations across time windows and powers inline ASCII visualizations generated by the bar function. By combining toDayOfWeek with conditional aggregations like countIf and the lag window function, queries uncover distinct morning surge trajectories between weekdays and weekends. The analysis demonstrates that weekday trip volumes peak around 8:45 AM alongside dropping speeds, whereas weekend volumes increase steadily across the morning.

## Context

Analyzing temporal patterns in large datasets requires efficient date and time manipulation once timestamps are stored in native DateTime columns.

## Approach / What changed

The NYC taxi dataset is loaded into a ClickHouse MergeTree table from Amazon S3 and analyzed using functions like toStartOfHour, toStartOfFifteenMinutes, toDate, dateDiff, formatDateTime, and toDayOfWeek alongside Time type casting and window functions.

## Takeaways

- Casting a DateTime expression to the Time type with ::Time strips the date component, allowing time-of-day filtering and cross-day interval grouping directly in ClickHouse queries.
- The dateDiff function computes durations between timestamp columns like pickup and dropoff times, which can filter out zero-duration records and derive metrics such as average travel speed.
- Combining toDayOfWeek with countIf and the lag window function enables side-by-side comparison of weekday and weekend period-over-period percentage changes in trip volume.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Open Source](https://yomu.fyi/topic/open-source)

- Source: [Clickhouse](https://clickhouse.com/blog/querying-datetimes)
- Source URL: https://clickhouse.com/blog/querying-datetimes
- Ingested by Yomu: 2026-08-28T01:22:46.953Z

[Read original post](https://clickhouse.com/blog/querying-datetimes)