Last month we [released clickhousectl](https://clickhouse.com/blog/introducing-clickhousectl-official-cli-for-clickhouse-local-and-cloud), a CLI for ClickHouse that manages local installations, runs local servers, and operates ClickHouse Cloud.

This is exciting for me as every month my colleague Tom Schreiber and I write the ClickHouse release post and we often compare query performances between versions. Before clickhousectl, we'd either have to use Docker or look through GitHub releases to find old binaries in order to do this.

Now we can just use clickhousectl and in this blog post we'll have a look at how to compare query performances between different versions for improvements made in recent versions. 

> <small>If you just want to see the query comparisons, you can skip forward to [DISTINCT over low cardinality columns](/blog/clickhousectl-compare-versions#distinct-low-cardinality) or [Parquet metadata cache](/blog/clickhousectl-compare-versions#parquet-metadata-cache).</small>

## Installing clickhousectl {#installing-clickhousectl}

But first things first, we need to get clickhousectl installed on our machine.
We can do this using the following command:

<pre><code type='click-ui' language='bash'>
curl https://clickhouse.com/cli | sh
</code></pre>

You should see something like the following output:

```shell
Detected platform: aarch64-apple-darwin
Fetching latest release...
Latest release: v0.1.18
Downloading https://github.com/ClickHouse/clickhousectl/releases/download/v0.1.18/clickhousectl-aarch64-apple-darwin...
Installed clickhousectl to /Users/markhneedham/.local/bin/clickhousectl
Created alias: chctl -> clickhousectl
```

We should now have a `clickhousectl` and `chctl` command, and if we run that we'll see the following output (trimmed for brevity):

```shell
The official CLI for ClickHouse: local and cloud

Usage: chctl <COMMAND>

Commands:
  local   Work with local ClickHouse installations
  cloud   Work with serverless ClickHouse in ClickHouse Cloud
  skills  Install ClickHouse agent skills into supported coding agents
  update  Update clickhousectl to the latest version
  help    Print this message or the help of the given subcommand(s)

Options:
  -h, --help     Print help
  -V, --version  Print version
```

## Installing local servers {#installing-local-servers}

We're going to install two ClickHouse versions on my machine - 25.12 and 26.3.
To have operations run locally, we use the `local` sub command.

We can download a version of ClickHouse by running this command:

<pre><code type='click-ui' language='bash'>
chctl local install 26.3
</code></pre>

I've actually got this version installed already, so I'll see the following output:

```shell
Resolving 26.3...
ClickHouse 26.3 is already installed as 26.3.10.16
Use --force to re-download the latest build
Installed version 26.3.10.16
```
If we use the `--force` flag that the output suggested, it will download the latest build of 26.3:

```shell
Resolving 26.3...
Downloading ClickHouse 26.3...
  [00:00:16] [###################################] 143.05 MiB/143.05 MiB (0s)
Detecting version...
Installed ClickHouse 26.3.10.30
Installed version 26.3.10.30
```

## Starting local servers {#starting-local-servers}

Next, let's have a look at how to start local servers.
First up, let's start a server running ClickHouse 25.12:

<pre><code type='click-ui' language='bash'>
chctl local server start --version 25.12 --name old
</code></pre>

We haven't actually installed this version yet, but that's ok - the CLI will automatically download it for us:

```shell
Resolving 25.12...
Downloading ClickHouse 25.12...
  [00:00:13] [#####################################] 111.51 MiB/111.51 MiB (0s)
Detecting version...
Installed ClickHouse 25.12.10.7
Server 'old' started in background (PID: 58145)
  HTTP port: 8123
  TCP port:  9000
  Version:   25.12.10.7
```

This server uses the default HTTP and TCP ports.

We can start a server running ClickHouse 26.3 as well:

<pre><code type='click-ui' language='bash'>
chctl local server start --version 26.3 --name new
</code></pre>

```shell
Resolving 26.3...
Note: 1 server already running (use `clickhousectl local server list` to see them)
Note: default ports in use, auto-assigned HTTP:8124 TCP:9001
Server 'new' started in background (PID: 58406)
  HTTP port: 8124
  TCP port:  9001
  Version:   26.3.10.30
```

This time it recognizes that there's already a server running and therefore uses different ports.

We can run the following command to check which servers are running:

<pre><code type='click-ui' language='bash'>
chctl local server list
</code></pre>

```shell
╭──────┬─────────┬───────┬────────────┬───────────┬──────────╮
│ Name │ Status  │ PID   │ Version    │ HTTP Port │ TCP Port │
├──────┼─────────┼───────┼────────────┼───────────┼──────────┤
│ new  │ running │ 58406 │ 26.3.10.30 │ 8124      │ 9001     │
│ old  │ running │ 58145 │ 25.12.10.7 │ 8123      │ 9000     │
╰──────┴─────────┴───────┴────────────┴───────────┴──────────╯
```

This shows the servers running for a given project, which means if we run the command from a different directory, we'll see the following output:

```shell
No servers
```

We can use the `--global` flag to show all servers running across our machine:

<pre><code type='click-ui' language='bash'>
chctl local server list --global
</code></pre>

```shell
╭──────┬─────────┬───────┬────────────┬───────────┬──────────┬──────────────╮
│ Name │ Status  │ PID   │ Version    │ HTTP Port │ TCP Port │ Project      │
├──────┼─────────┼───────┼────────────┼───────────┼──────────┼──────────────┤
│ old  │ running │ 58145 │ 25.12.10.7 │ 8123      │ 9000     │ .../ch-test  │
│ new  │ running │ 58406 │ 26.3.10.30 │ 8124      │ 9001     │ .../ch-test  │
╰──────┴─────────┴───────┴────────────┴───────────┴──────────┴──────────────╯
```

## Loading data into ClickHouse {#loading-data}

Now that we've got our servers running, it's time to load some data.
We'll first connect to the 25.12 server:

<pre><code type='click-ui' language='bash'>
chctl local client --name old -mn
</code></pre>

And then run the following query to create the [UK price paid](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid) table:

<pre><code type='click-ui' language='sql'>
CREATE OR REPLACE TABLE uk_price_paid
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3,
               'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (date, postcode1, postcode2, addr1, addr2)
SETTINGS add_minmax_index_for_numeric_columns=1,
         add_minmax_index_for_string_columns;
</code></pre>

For simplicity's sake, I've got this data in Parquet format and I'm serving it locally via a HTTP server.
We can import it like this:

<pre><code type='click-ui' language='sql'>
INSERT INTO uk_price_paid 
SELECT * FROM url('http://127.0.0.1:8000/uk_all.parquet');
</code></pre>

```shell
30452463 rows in set. Elapsed: 8.990 sec. Processed 30.45 million rows, 170.44 MB (3.39 million rows/s., 18.96 MB/s.)
Peak memory usage: 2.00 GiB.
```

30 million rows imported! Now we need to get the same data into the 26.3 server.

One of the handy things about `clickhousectl` is that you can copy a table schema from one local server to another entirely from the CLI, without touching any files. First, fetch the create table statement from the old server:

<pre><code type='click-ui' language='bash'>
chctl local client --name old \
  --query "SHOW CREATE TABLE uk_price_paid" \
  --output-format LineAsString
</code></pre>

```shell
CREATE TABLE default.uk_price_paid
(
    `price` UInt32,
    `date` Date,
    `postcode1` LowCardinality(String),
    `postcode2` LowCardinality(String),
    `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4),
    `is_new` UInt8,
    `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2),
    `addr1` String,
    `addr2` String,
    `street` LowCardinality(String),
    `locality` LowCardinality(String),
    `town` LowCardinality(String),
    `district` LowCardinality(String),
    `county` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (date, postcode1, postcode2, addr1, addr2)
SETTINGS index_granularity = 8192
```

By default, `SHOW CREATE TABLE` returns its output wrapped in a table with a header and borders. `LineAsString` treats each row of the result as a plain string and prints it as-is, with no decorators — just the raw SQL. We can then pipe that straight into the 26.3 server in one command:

<pre><code type='click-ui' language='bash'>
chctl local client --name old \
  --query "SHOW CREATE TABLE uk_price_paid" \
  --output-format LineAsString |
chctl local client --name new
</code></pre>

The table now exists on the new server. To copy the data across, we connect to the 26.3 server and use the [`remote`](https://clickhouse.com/docs/sql-reference/table-functions/remote) table function to read directly from the 25.12 server:

<pre><code type='click-ui' language='bash'>
chctl local client --name new -mn
</code></pre>

<pre><code type='click-ui' language='sql'>
INSERT INTO uk_price_paid
SELECT * 
FROM remote('localhost:9000', 'default', 'uk_price_paid');
</code></pre>

```shell
30452463 rows in set. Elapsed: 15.169 sec. Processed 30.45 million rows, 1.33 GB (2.01 million rows/s., 87.96 MB/s.)
Peak memory usage: 339.23 MiB.
```

This is a useful pattern any time you want to copy data from one local server to another.

## DISTINCT over low cardinality columns (added in 26.1) {#distinct-low-cardinality}

Now it's time to test a performance improvement made to DISTINCT over low cardinality columns in ClickHouse 26.1.
Below is a slide from Alexey's 26.1 release call slide deck:

[![slide-26.1-16.png](/uploads/slide_26_1_16_2669b51bf6.png)](https://presentations.clickhouse.com/2026-release-26.1/?full#16)

We'll first run the query 5 times against 25.12:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do 
  chctl local client \
    --name old \
    --query 'SELECT distinct(county) FROM uk_price_paid' \
    --time \
    -- --output-format Null;
done
</code></pre>

```shell
0.076
0.082
0.075
0.074
0.073
```

The `--time` flag outputs times in seconds, so the best time here is 0.076 seconds (76ms).

Now let's do the same against 26.3:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do 
  chctl local client \
    --name new \
    --query 'SELECT distinct(county) FROM uk_price_paid' \
    --time \
    -- --output-format Null;
done
</code></pre>

```shell
0.015
0.016
0.014
0.017
0.014
```

The best time here is 0.014 seconds (14ms), which is a little more than 5 times faster than on ClickHouse 25.12.
Success!

## Parquet metadata cache (added in 26.3) {#parquet-metadata-cache}

In the 26.3 release last month, we introduced a metadata cache that stores Parquet file footer metadata (structure, row group layout, column statistics), keyed by ETag for consistency.

[![slide-31.png](/uploads/slide_31_711f184015.png)](https://presentations.clickhouse.com/2026-release-26.3/?full#31)

This will be especially useful when running repeat queries on Parquet files in S3.

I asked Claude Code to recommend a dataset containing Parquet files and it suggested the [AWS public blockchain dataset](https://registry.opendata.aws/aws-public-blockchain/).

The following query, which we'll save to `parquet.sql`, returns the number of rows and average gas price for block numbers between 19500000 and 19501000.

<pre><code type='click-ui' language='sql'>
SELECT count(), avg(gas_price)
FROM s3(
  's3://aws-public-blockchain/v1.0/eth/transactions/date=2024-*/*.parquet',
  NOSIGN,
  Parquet
)
WHERE block_number BETWEEN 19500000 AND 19501000
SETTINGS use_query_condition_cache=0;
</code></pre>

We'll first run the query five times against 25.12 by passing the file name in using the `--queries-file` flag:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do
  chctl local client --name old --queries-file parquet.sql --time;
done
</code></pre>

```shell
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
11.480
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.473
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.102
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.604
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
8.957
```

And now the same query against 26.3:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do
  chctl local client --name new --queries-file parquet.sql --time;
done
</code></pre>

```shell
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
12.463
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
2.224
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.696
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.639
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.687
```

The initial run takes around 11-12 seconds on both versions. Subsequent runs take around 8-9 seconds on ClickHouse 25.12, which is possibly due to file system caching.

With 26.3, we see a huge reduction in the query time of subsequent runs down to around 1-2 seconds, which is around 5 times faster than it was on the first run.

## Inspecting the Parquet metadata cache {#inspecting-parquet-metadata-cache}

There are a couple of system tables that we can query to learn more about the Parquet metadata cache.

First up, there are some settings that we can query via `system.server_settings`:

<pre><code type='click-ui' language='sql'>
SELECT name, value
FROM system.server_settings
WHERE name ILIKE '%parquet_metadata_cache%';
</code></pre>

```shell
   ┌─name───────────────────────────────┬─value─────┐
1. │ parquet_metadata_cache_policy      │ SLRU      │
2. │ parquet_metadata_cache_size        │ 536870912 │
3. │ parquet_metadata_cache_max_entries │ 5000      │
4. │ parquet_metadata_cache_size_ratio  │ 0.5       │
   └────────────────────────────────────┴───────────┘
```

The cache size is 512MB or 5,000 entries, whichever value's reached first!

We can also query `system.metrics` to see how much data we've populated in the cache:

<pre><code type='click-ui' language='sql'>
SELECT name, value
FROM system.metrics
WHERE name ILIKE '%parquet%metadata%';
</code></pre>

```shell
   ┌─name──────────────────────┬────value─┐
1. │ ParquetMetadataCacheBytes │ 46067740 │
2. │ ParquetMetadataCacheFiles │      366 │
   └───────────────────────────┴──────────┘
```

We have the metadata cached for 366 files and it's taking up 46MB of space.

## Conclusion {#conclusion}

In this post we've seen how `clickhousectl` makes it easy to spin up multiple ClickHouse versions side by side and compare query performance between them.

We demonstrated two improvements that shipped in recent releases: 

* A 5x speedup for DISTINCT queries over low cardinality columns (introduced in 26.1)
* A Parquet metadata cache (introduced in 26.3) that reduces repeat S3 query times from ~9 seconds down to ~1-2 seconds.

If you want to try this yourself, install `clickhousectl` with:

<pre><code type='click-ui' language='bash'>
curl https://clickhouse.com/cli | sh
</code></pre>

And check the [clickhousectl documentation](https://clickhouse.com/docs/interfaces/cli) for more on what you can do with it.


Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Get started today

We use clickhousectl to spin up multiple ClickHouse versions side by side and benchmark two recent performance improvements.

large_Comparing ClickHouse Versions.jpg

medium_Comparing ClickHouse Versions.jpg

small_Comparing ClickHouse Versions.jpg

thumbnail_Comparing ClickHouse Versions.jpg

Comparing ClickHouse Versions.jpg

Comparing ClickHouse versions with clickhousectl

Hello, and welcome to the April 2026 ClickHouse newsletter!

This month, we have a new CLI for ClickHouse local and cloud, agentic coding at ClickHouse, materialized CTEs in our latest release, building a SIEM, and more!

## Featured community member: Nazarii Piontko {#featured-community-member}

This month's featured community member is Nazarii Piontko, Solutions Architect at Future Processing.  

![](/uploads/newsletter_apr2026_image2_c4a00ae7d0.png)

He leads technical presales and architecture, translating complex requirements into solutions for clients across trading, mobility, and logistics. He stays hands-on through open-source contributions to distributed data systems, mentors engineers, and has taught at universities.

Nazarii contributed two features to ClickHouse 26.3 LTS: the [`naturalSortKey`](https://github.com/ClickHouse/ClickHouse/pull/90322) function, which enables intuitive sorting of strings containing numbers (so "file10" sorts after "file9", not "file1"), and support for the [ALP compression codec](https://github.com/ClickHouse/ClickHouse/pull/91362), which brings efficient lossless compression for floating-point data.

➡️ [Connect with Nazarii on LinkedIn](https://www.linkedin.com/in/piontko/)

## Open House 2026 {#open-house}

It's just over a month until the second edition of [Open House](https://clickhouse.com/openhouse/san-francisco), a free three-day ClickHouse user conference running May 26–28 in San Francisco.

Kick things off on May 26 with hands-on workshops covering real-time analytics, observability, database administration, and data warehousing, then head into two days of keynotes, technical sessions, and networking.

Hear from ClickHouse CEO Aaron Katz and CTO Alexey Milovidov, plus industry guests including Bret Taylor (Sierra) and Guillermo Rauch (Vercel). Admission is free!

➡️ [Register now](https://clickhouse.com/openhouse/san-francisco)

## 26.3 release {#26-3-release}

![](/uploads/newsletter_apr2026_image1_ce952c56f3.png)

26.3 sees the experimental introduction of materialized Common Table Expressions (CTEs) and WebAssembly User-defined functions (UDFs).

This release also makes asynchronous inserts the default, enables JOIN reordering for ANTI, SEMI, and FULL, and improves the internal storage of the ClickHouse Map data type.

➡️ [Read the release post](https://clickhouse.com/blog/clickhouse-release-26-03)

## Introducing clickhousectl: the CLI for ClickHouse local and cloud (beta) {#introducing-clickhousectl}

![april_2026_clickhouse-ctl.png](/uploads/april_2026_clickhouse_ctl_e3d28f3583.png)

Alasdair Brown announced clickhousectl, a CLI for managing both local ClickHouse installations and cloud deployments, built with AI agents in mind alongside human developers.

Designed for agentic development, it handles local version management, project scaffolding, and server lifecycle, while also supporting cloud infrastructure commands with authentication safeguards like read-only OAuth and permissioned API keys.

➡️ [Read the blog post](https://clickhouse.com/blog/introducing-clickhousectl-official-cli-for-clickhouse-local-and-cloud)

## How Goldsky made historical blockchain data backfills 12x faster {#how-goldsky-made-historical-blockchain-data-backfills-12x-faster}

![](/uploads/newsletter_apr2026_image6_7949daf91e.png)

Goldsky sped up historical blockchain backfills by 12x by replacing their Kafka/Avro pipeline with direct ClickHouse reads using the Apache Arrow format.

Eliminating deserialization overhead by reading data in the format closest to its physical storage proved extremely efficient, boosting throughput from ~50K to ~600K rows/second.

➡️ [Read the blog post](https://goldsky.com/blog/making-historical-blockchain-backfills-faster)

## Agentic coding at ClickHouse {#agentic-coding-at-clickhouse}

![](/uploads/newsletter_apr2026_image7_ffb09aaaf1.png)

Our CTO, Alexey Milovidov, argues that agentic coding has crossed the threshold into genuine usefulness for professional software development, driven by recent model improvements such as Claude Opus 4.5.

He walks through how we've achieved productivity gains by using AI agents across a wide range of tasks - fixing flaky tests, investigating bugs, code reviews, security research, and more.

The key takeaway: As of today, agents work best as pair programmers under an engineer's direction, not as autonomous replacements.

➡️ [Read the blog post](https://clickhouse.com/blog/agentic-coding)

## Building a SIEM with ClickHouse and Clickdetect {#building-a-siem-with-clickhouse-and-clickdetect}

![](/uploads/newsletter_apr2026_image3_f7c3e467cd.png)

Vinicius Morais walks through building a SIEM architecture using ClickHouse and Clickdetect, an open-source detection engine that runs SQL-based security rules against your log data.

He uses Wazuh (an open-source security platform) solely as a log collector and decoder, forwarding parsed alerts to a ClickHouse table with compression codecs, TTLs, bloom filter indexes, and S3 storage support.

From there, Clickdetect schedules detections and fires alerts to a webhook — giving you a lightweight, cost-effective SIEM that scales from a single node to a distributed ClickHouse cluster.

➡️ [Read the blog post](https://medium.com/@souzo/building-a-powerful-siem-with-clickhouse-and-clickdetect-ae68a4495a76)

## Building high-performance full-text search for object storage {#building-high-performance-full-text-search-for-object-storage}

![](/uploads/newsletter_apr2026_image8_39f30f112c.png)

Last month, we announced that Full-text search was generally available. We now have a blog post written by the engineering team explaining how they redesigned the FTS index to work efficiently with object storage, where random reads are expensive.

The new index uses front-coding compression, a sparse lookup index, and adaptive posting lists, allowing many queries to be answered from the index alone without touching the underlying text columns.

➡️ [Read the blog post](https://hookdeck.com/blog/how-we-made-payload-search-60x-faster-in-clickhouse)

## Quick reads {#quick-reads}

* Marcin Kulakowski explains [why ClickHouse is becoming the data control plane](https://mkulakowski2-73849.medium.com/the-rise-of-real-time-data-why-clickhouse-is-becoming-the-control-plane-77b59bf85399).  
* Divyanshu shares what he [learned about observability (and ClickHouse) in 10 days](https://medium.com/@shekdivyanshu/what-i-learned-about-observability-in-10-days-a370c143d2ee).  
* Alexandra Petra Berger [draws parallels](https://medium.com/towards-data-engineering/poyekhali-what-1961-soviet-space-tech-teaches-us-about-modern-data-engineering-1549445a054f) between Sergei Korolev's space engineering principles - precision, redundancy, and military-grade quality checks - and modern data engineering practices, using ClickHouse compression codecs like Gorilla and DoubleDelta to illustrate how the same rigor applies to building reliable data systems.

## Upcoming events {#upcoming-events}

### Global virtual events

* [Combining Postgres & ClickHouse to Build a Unified Data Stack](https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres) - Apr 22, 2026  
* [CDC ClickPipes Webinar](https://clickhouse.com/company/events/202604-EMEA-Webinar-CDC-ClickPipes) - Apr 23, 2026  
* [Analisando bilhões de registros em tempo real](https://clickhouse.com/company/events/202604-LATAM-Real-Time-Analytics) - Apr 16, 2026  
* [Analizando miles de millones de registros en tiempo real](https://clickhouse.com/company/events/202605-LATAM-Real-Time-Analytics-ES) - May 14, 2026

### Virtual training

* [ClickHouse Fundamentals](https://clickhouse.com/company/events/202605-AMER-ClickHouse-Fundamentals) - May 5, 2026  
* [Data Warehousing with ClickHouse: Level 1](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level1) - May 13, 2026  
* [Data Warehousing with ClickHouse: Level 2](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level2) - May 14, 2026  
* [Observability with ClickStack: Level 1](https://clickhouse.com/company/events/202605-AMER-EMEA-Observability-with-ClickStack-Level1) - May 19, 2026  
* [Data Warehousing with ClickHouse: Level 3](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level3) - May 20, 2026  
* [ClickHouse Fundamentals](https://clickhouse.com/company/events/202605-EMEA-ClickHouse-Fundamentals) - May 21, 2026

### Events in AMER

* [Meetup in Sunnyvale](https://clickhouse.com/company/events/202504164) - Sunnyvale - Apr 16, 2026  
* [Google Cloud Next 2026](https://clickhouse.com/company/events/google-cloud-next-2026) - Las Vegas - Apr 22, 2026  
* [House Party, Google Cloud Next](https://clickhouse.com/company/events/2026-houseparty-google-next) - Las Vegas - Apr 22, 2026  
* [Sao Paulo 2-day In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202605-LATAM-SaoPaulo-Real-time-Analytics-w-ClickHouse) - Sao Paulo - May 12, 2026  
* [Open House](https://clickhouse.com/company/events/202605-global-open-house) - San Francisco - May 26, 2026

### Events in EMEA

* [Paris In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-EMEA-Paris-Real-time-Analytics-w-ClickHouse) - Paris - Apr 15, 2026  
* [GrafanaCON](https://grafana.com/events/grafanacon/) - Barcelona - Apr 20-22, 2026  
* [Barcelona In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-EMEA-Barcelona-Real-time-Analytics-w-ClickHouse) - Barcelona - Apr 20, 2026  
* [AWS Summit London](https://aws.amazon.com/events/summits/london/) - London - Apr 22 - Booth G18  
* [Rise of AI Berlin](https://riseof.ai/conference-2026/) - Berlin - May 5-6, 2026  
* [AWS Summit Tel Aviv](https://aws.amazon.com/events/summits/tel-aviv/) - Tel Aviv - May 6, 2026  
* [Data Innovation Summit](https://datainnovationsummit.com/) - Stockholm - May 6-8, 2026  
* [Gartner Data & Analytics](http://gartner.com/en/data-analytics) - London - May 11-13, 2026  
* [Revolution Banking](https://www.revolutionbanking.es/) - Madrid - May 12, 2026  
* [London 2-day In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202605-EMEA-London-Real-time-Analytics-w-ClickHouse) - London - May 19, 2026  
* [ClickHouse Meetup London](https://www.meetup.com/clickhouse-london-user-group/events/313759007/) - London - May 19, 2026  
* [Platforma 2026](https://www.platfor-ma.com/) - Tel Aviv - May 20, 2026  
* [AWS Summit Hamburg](https://aws.amazon.com/events/summits/hamburg/) - Hamburg - May 20, 2026  
* [Google Summit Madrid](https://cloudonair.withgoogle.com/events/cloud-ai-live-madrid-2026) - Madrid - May 28, 2026

### Events in APAC

* [Data Streaming World Tour - Mumbai](https://clickhouse.com/company/events/202604-APJ-3P-Mumbai-ConfluentStreamingTour) - Apr 13, 2026  
* [Taiwan In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-APJ-Taiwan-Real-time-Analytics-with-ClickHouse) - Taiwan - Apr 16, 2026  
* [Taipei Open Source Meetup](https://clickhouse.com/company/events/taipei-open-source-meetup) - Taipei - Apr 16, 2026  
* [Data Streaming World Tour - Bangalore](https://clickhouse.com/company/events/202604-APJ-3P-Bangalore-ConfluentStreamingTour) - Apr 16, 2026  
* [PagerDuty on Tour Tokyo](https://clickhouse.com/company/events/202604-APJ-3P-Tokyo-PagerDutyTour) - Tokyo - Apr 16, 2026  
* [Bangalore Meetup with Alexey Milovidov](https://www.meetup.com/clickhouse-bangalore-user-group/events/313739871/) - Apr 18, 2026  
* [Data Streaming World Tour - Jakarta](https://clickhouse.com/company/events/202604-APJ-3P-Jakarta-ConfluentStreamingTour) - Apr 21, 2026  
* [Ho Chi Minh In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-APJ-HoChiMinh-Real-time-Analytics-with-ClickHouse) - Ho Chi Minh - Apr 22, 2026  
* [AWS Summit Bengaluru](https://aws.amazon.com/events/summits/bengaluru/) - Apr 22-23, 2026  
* [Microsoft AI Tour - Sydney](https://clickhouse.com/company/events/202604-APJ-3P-Sydney-MicrosoftAITour) - Apr 23, 2026  
* [AWS Summit Singapore](https://clickhouse.com/company/events/202605-APJ-3P-Singapore-AWSSummit) - Singapore - May 6, 2026  
* [AWS Summit Sydney](https://clickhouse.com/company/events/202605-APJ-AWSSummit-Sydney) - Sydney - May 13, 2026  
* [Data Engineering Summit](https://clickhouse.com/company/events/202605-APJ-3P-Bangalore-DataEngineeringSummit) - Bangalore - May 14, 2026  
* [AWS Summit Seoul](https://clickhouse.com/company/events/202605-APJ-3P-Seoul-AWSSummit) - Seoul - May 20, 2026  
* [Findy VPoE Summit](https://clickhouse.com/company/events/202605-APJ-3P-Tokyo-FindyVPoESummit) - Tokyo - May 22, 2026


Welcome to the April 2026 ClickHouse newsletter, which will round up what’s happened in real-time data warehouses over the last month.

large_newsletter_apr2026_image4.png

medium_newsletter_apr2026_image4.png

small_newsletter_apr2026_image4.png

thumbnail_newsletter_apr2026_image4.png

newsletter_apr2026_image4.png

April 2026 newsletter

Learn how ClickHouse uses primary indexes, lightweight projections, and skip indexes to prune data before reading it. Demonstrated  on a 243 million row UK property sales dataset.

large_Image 577391624 1200x630.jpg

medium_Image 577391624 1200x630.jpg

small_Image 577391624 1200x630.jpg

thumbnail_Image 577391624 1200x630.jpg

Image 577391624 1200x630.jpg

Index-based pruning in ClickHouse

In this post we're going to look at some of the most useful ClickHouse functions for querying and filtering by dates and datetimes - things like rounding to the nearest hour or 15-minute window, filtering by time of day, and computing durations between two timestamps.

If you're working with raw date strings or timestamps that need converting first, check out another post that I wrote about [parsing dates and datetimes in ClickHouse](https://clickhouse.com/blog/parsing-dates-datetimes). This post picks up from there and focuses on what to do once your data is already in a `DateTime` column.


<iframe width="768" height="432" src="https://www.youtube.com/embed/odWa7rAtI5k?si=PkUSvscWY19bmDcu" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Importing the New York City taxi dataset {#importing_the_new_york_city_taxi_dataset}

Our dataset of choice is the [New York City taxi dataset](https://clickhouse.com/docs/getting-started/example-datasets/nyc-taxi), so let's get that set up in ClickHouse. First we'll create a database:

<pre><code type='click-ui' language='sql'>
CREATE DATABASE nyc_taxi;
</code></pre>

And a table:

<pre><code type='click-ui' language='sql'>
CREATE TABLE nyc_taxi.trips_small (
    trip_id             UInt32,
    pickup_datetime     DateTime,
    dropoff_datetime    DateTime,
    pickup_longitude    Nullable(Float64),
    pickup_latitude     Nullable(Float64),
    dropoff_longitude   Nullable(Float64),
    dropoff_latitude    Nullable(Float64),
    passenger_count     UInt8,
    trip_distance       Float32,
    fare_amount         Float32,
    extra               Float32,
    tip_amount          Float32,
    tolls_amount        Float32,
    total_amount        Float32,
    payment_type        Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
    pickup_ntaname      LowCardinality(String),
    dropoff_ntaname     LowCardinality(String)
)
ENGINE = MergeTree
PRIMARY KEY (pickup_datetime, dropoff_datetime);
</code></pre>

We can then run the following command to import the data:

<pre><code type='click-ui' language='sql'>
INSERT INTO nyc_taxi.trips_small
SELECT
    trip_id,
    pickup_datetime,
    dropoff_datetime,
    pickup_longitude,
    pickup_latitude,
    dropoff_longitude,
    dropoff_latitude,
    passenger_count,
    trip_distance,
    fare_amount,
    extra,
    tip_amount,
    tolls_amount,
    total_amount,
    payment_type,
    pickup_ntaname,
    dropoff_ntaname
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
    'TabSeparatedWithNames'
);
</code></pre>

This query imports just over 3 million records, as we can see in the output below:

```shell
3000317 rows in set. Elapsed: 32.077 sec. Processed 3.00 million rows, 256.38 MB (93.53 thousand rows/s., 7.99 MB/s.)
Peak memory usage: 536.51 MiB.
```

We can adjust the `{0..2}` in the URI to include more files if we want to import more data.

Now that we're loaded the data, it's time to write some queries. 
We'll be working with the `pickup_datetime` and `dropoff_datetime` columns:

## Trips by hour {#trips_by_hour}

Let's start by exploring taxi journeys taken on July 1st, 2015. We'll use [`toStartOfHour`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tostartofhour) to round datetimes down to the nearest hour, [`toDate`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todate) to filter to a single day, and [`toHour`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tohour) to restrict to morning hours:

<pre><code type='click-ui' language='sql'>
SELECT
  toStartOfHour(pickup_datetime) as hour,
  count() as trips,
  round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toDate(pickup_datetime) = '2015-07-01'
AND toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
</code></pre>

```shell
┌────────────────hour─┬─trips─┬─avg_passengers─┐
│ 2015-07-01 00:00:00 │   663 │            1.7 │
│ 2015-07-01 01:00:00 │   381 │            1.6 │
│ 2015-07-01 02:00:00 │   249 │            1.8 │
│ 2015-07-01 03:00:00 │   155 │            1.6 │
│ 2015-07-01 04:00:00 │   159 │            1.5 │
│ 2015-07-01 05:00:00 │   197 │            1.5 │
│ 2015-07-01 06:00:00 │   530 │            1.6 │
│ 2015-07-01 07:00:00 │   849 │            1.6 │
│ 2015-07-01 08:00:00 │  1034 │            1.6 │
│ 2015-07-01 09:00:00 │  1033 │            1.7 │
│ 2015-07-01 10:00:00 │   898 │            1.7 │
│ 2015-07-01 11:00:00 │   900 │            1.6 │
│ 2015-07-01 12:00:00 │   961 │            1.7 │
└─────────────────────┴───────┴────────────────┘
```

It was quiet overnight, trips start to pick up around 6am, and peak around 8–9am. But was July 1st typical? Let's remove the date filter and look across all days. To do that we cast `toStartOfHour` down to a `Time` type using [`::Time`](https://clickhouse.com/docs/en/sql-reference/data-types/time) - that strips out the date and leaves just the time, so all days are grouped together:

<pre><code type='click-ui' language='sql'>
SELECT
  toStartOfHour(pickup_datetime)::Time as hour,
  count() as trips,
  round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
</code></pre>

```shell
┌─────hour─┬──trips─┬─avg_passengers─┐
│ 00:00:00 │ 118268 │            1.7 │
│ 01:00:00 │  86495 │            1.7 │
│ 02:00:00 │  65246 │            1.7 │
│ 03:00:00 │  47377 │            1.7 │
│ 04:00:00 │  34840 │            1.7 │
│ 05:00:00 │  32328 │            1.6 │
│ 06:00:00 │  68644 │            1.6 │
│ 07:00:00 │ 107494 │            1.6 │
│ 08:00:00 │ 132596 │            1.6 │
│ 09:00:00 │ 136228 │            1.6 │
│ 10:00:00 │ 134286 │            1.7 │
│ 11:00:00 │ 137561 │            1.7 │
│ 12:00:00 │ 145282 │            1.7 │
└──────────┴────────┴────────────────┘
```

When we look across all days we still see a reduction in journeys over night, but the increase starts a couple of hours earlier, from 6am to 7am. The number of trips then stays reasonably stable for the next four hours.

## Rush hour in 15-minute windows {#rush_hour}

When exactly does the morning rush start? Let's zoom in using [`toStartOfFifteenMinutes`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tostartoffifteenminutes) to bucket by 15-minute intervals, and [`formatDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#formatdatetime) to make the output readable. In the `WHERE` clause we cast `pickup_datetime` to `Time` to filter just by time of day - no date needed:

<pre><code type='click-ui' language='sql'>
SELECT
  formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
  count() as trips,
  round(avg(trip_distance), 2) as avgDistance
FROM nyc_taxi.trips_small
WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
AND trip_distance > 0
GROUP BY timeWindow
ORDER BY timeWindow;
</code></pre>

```shell
┌─timeWindow─┬─trips─┬─avgDistance─┐
│ 06:00 AM   │ 11601 │        4.47 │
│ 06:15 AM   │ 14645 │        3.97 │
│ 06:30 AM   │ 19033 │        3.67 │
│ 06:45 AM   │ 22795 │         3.2 │
│ 07:00 AM   │ 23179 │        3.27 │
│ 07:15 AM   │ 25465 │        3.12 │
│ 07:30 AM   │ 28350 │        3.04 │
│ 07:45 AM   │ 29914 │        2.89 │
│ 08:00 AM   │ 30444 │           3 │
│ 08:15 AM   │ 32063 │        2.91 │
│ 08:30 AM   │ 34293 │         2.8 │
│ 08:45 AM   │ 35116 │        2.63 │
│ 09:00 AM   │ 33776 │        2.74 │
│ 09:15 AM   │ 33800 │        2.72 │
│ 09:30 AM   │ 33694 │        2.73 │
│ 09:45 AM   │ 34235 │        2.63 │
└────────────┴───────┴─────────────┘
```

The surge begins at 6:30, accelerates until 7:30, and peaks at 8:45.

## What does rush hour feel like? {#rush_hour_speed}

We know when rush hour starts - but what does it feel like if you're in a taxi? We can use [`dateDiff`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#datediff) to compute journey duration in minutes, which lets us calculate average speed. We also use `dateDiff` in the `WHERE` clause to filter out zero-duration trips (bad data):

<pre><code type='click-ui' language='sql'>
WITH buckets AS (
  SELECT
    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
    count() as trips,
    round(avg(trip_distance), 2) as avgDist,
    round(avg(dateDiff('minute', pickup_datetime, dropoff_datetime)), 1) AS avgDuration,
    round(avg(
      trip_distance /
      (dateDiff('minute', pickup_datetime, dropoff_datetime) / 60)
    ), 1) AS avgSpeed
  FROM nyc_taxi.trips_small
  WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
  AND trip_distance > 0
  AND dateDiff('minute', pickup_datetime, dropoff_datetime) > 0
  GROUP BY timeWindow
  ORDER BY timeWindow
)
SELECT timeWindow, trips, avgDuration, avgDist, avgSpeed,
       bar(avgSpeed, 0, (SELECT max(avgSpeed) FROM buckets), 20) AS speedBar
FROM buckets
ORDER BY timeWindow ASC;
</code></pre>

```shell
┌─timeWindow─┬─trips─┬─avgDuration─┬─avgDist─┬─avgSpeed─┬─speedBar─────────────┐
│ 06:00 AM   │ 11562 │        13.2 │    4.48 │     19.3 │ ████████████████████ │
│ 06:15 AM   │ 14609 │        13.1 │    3.98 │     18.2 │ ██████████████████▊  │
│ 06:30 AM   │ 18993 │        12.5 │    3.67 │     17.3 │ █████████████████▉   │
│ 06:45 AM   │ 22754 │        11.5 │    3.21 │     16.1 │ ████████████████▋    │
│ 07:00 AM   │ 23139 │        12.3 │    3.27 │     15.4 │ ███████████████▉     │
│ 07:15 AM   │ 25428 │        12.7 │    3.12 │     14.3 │ ██████████████▊      │
│ 07:30 AM   │ 28313 │        13.9 │    3.04 │     13.5 │ █████████████▉       │
│ 07:45 AM   │ 29873 │        13.8 │    2.89 │     12.7 │ █████████████▏       │
│ 08:00 AM   │ 30411 │        14.2 │       3 │       12 │ ████████████▍        │
│ 08:15 AM   │ 32017 │        15.2 │    2.91 │     11.5 │ ███████████▉         │
│ 08:30 AM   │ 34258 │        15.4 │     2.8 │       11 │ ███████████▍         │
│ 08:45 AM   │ 35071 │        14.9 │    2.64 │     10.8 │ ███████████▏         │
│ 09:00 AM   │ 33718 │        15.3 │    2.74 │     10.9 │ ███████████▎         │
│ 09:15 AM   │ 33754 │        15.3 │    2.72 │     10.8 │ ███████████▏         │
│ 09:30 AM   │ 33657 │        15.4 │    2.73 │     10.8 │ ███████████▏         │
│ 09:45 AM   │ 34188 │        14.8 │    2.63 │     10.9 │ ███████████▎         │
└────────────┴───────┴─────────────┴─────────┴──────────┴──────────────────────┘
```

The [`bar`](https://clickhouse.com/docs/en/sql-reference/functions/other-functions#bar) function draws an ASCII bar chart scaled to the max value - a handy way to visualize relative values inline.

At 6am taxis are moving at just over 19 mph. By 8am that's down to around 12 mph - slow, but pretty standard for a big city during rush hour. It keeps getting slower as the morning wears on.

## Weekdays vs weekends {#weekdays_vs_weekends}

We've confirmed rush hour exists - but does it happen on weekends too? We can split weekday and weekend trips using [`countIf`](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-if) with [`toDayOfWeek`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todayofweek): values 1–5 are weekdays, 6–7 are weekends. We then use the [`lag`](https://clickhouse.com/docs/en/sql-reference/window-functions) window function to compute the percentage change in trips between each 15-minute window:

<pre><code type='click-ui' language='sql'>
WITH trips AS (
  SELECT
    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
    countIf(toDayOfWeek(pickup_datetime) <= 5) as wdTrips,
    countIf(toDayOfWeek(pickup_datetime) > 5) as weTrips
  FROM nyc_taxi.trips_small
  WHERE trip_distance > 0
  AND pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
  GROUP BY timeWindow
  ORDER BY timeWindow
)
SELECT timeWindow, wdTrips,
  round((
    (wdTrips - lag(wdTrips) OVER (ORDER BY timeWindow)) /
    lag(wdTrips) OVER (ORDER BY timeWindow)) * 100,
  1) as wdPctChange,
  weTrips,
  round(
    ((weTrips - lag(weTrips) OVER (ORDER BY timeWindow)) /
    lag(weTrips) OVER (ORDER BY timeWindow)) * 100,
  1) as wePctChange
FROM trips
ORDER BY timeWindow;
</code></pre>

```shell
┌─timeWindow─┬─wdTrips─┬─wdPctChange─┬─weTrips─┬─wePctChange─┐
│ 06:00 AM   │    9398 │         inf │    2203 │         inf │
│ 06:15 AM   │   12254 │        30.4 │    2391 │         8.5 │
│ 06:30 AM   │   16106 │        31.4 │    2927 │        22.4 │
│ 06:45 AM   │   19727 │        22.5 │    3068 │         4.8 │
│ 07:00 AM   │   20285 │         2.8 │    2894 │        -5.7 │
│ 07:15 AM   │   22129 │         9.1 │    3336 │        15.3 │
│ 07:30 AM   │   24494 │        10.7 │    3856 │        15.6 │
│ 07:45 AM   │   25681 │         4.8 │    4233 │         9.8 │
│ 08:00 AM   │   26259 │         2.3 │    4185 │        -1.1 │
│ 08:15 AM   │   27509 │         4.8 │    4554 │         8.8 │
│ 08:30 AM   │   28891 │           5 │    5402 │        18.6 │
│ 08:45 AM   │   29154 │         0.9 │    5962 │        10.4 │
│ 09:00 AM   │   27872 │        -4.4 │    5904 │          -1 │
│ 09:15 AM   │   27268 │        -2.2 │    6532 │        10.6 │
│ 09:30 AM   │   26426 │        -3.1 │    7268 │        11.3 │
│ 09:45 AM   │   26070 │        -1.3 │    8165 │        12.3 │
└────────────┴─────────┴─────────────┴─────────┴─────────────┘
```

Weekdays show a sharp spike at 6:15–6:30, a second (smaller) increase around 7:15–7:30, and peak at 8:45 before leveling off. Weekends are different: the increase at 6:30 is gentler, and trips keep growing steadily well into the morning.


How to query datetime columns in ClickHouse - from hourly bucketing to rush hour analysis with real taxi data.

large_ClickHouse Blog Banner (4).jpg

medium_ClickHouse Blog Banner (4).jpg

small_ClickHouse Blog Banner (4).jpg

thumbnail_ClickHouse Blog Banner (4).jpg

ClickHouse Blog Banner (4).jpg

Querying DateTimes in ClickHouse

Hello, and welcome to the March 2026 ClickHouse newsletter!

This month, we have an overview of Geospatial, the launch of chDB 4, how Hookdeck made payload search 60 times faster, The Agentic Data Stack, and more!

## Featured community member: Jamie Herre {#featured-community-member}

This month's featured community member is Jamie Herre, Sr. Director of Engineering at Cloudflare.

![](/uploads/mar2026_nl_image4_2c5abe927e.png)

Jamie leads engineering on Cloudflare's analytics infrastructure - a system that processes over 1.61 quadrillion events every day across more than 300 global data centers, built on ClickHouse.

At the <a href="https://clickhouse.com/blog/cloudflare" target="_blank">ClickHouse meetup in August 2025</a>, Jamie shared how his team designed for both explosive growth and catastrophic failure simultaneously. In one live demonstration, a single query scanned 96 trillion events in under 2 seconds - while a simulated North American outage caused European clusters to silently absorb the load without missing a beat.

➡️ <a href="https://www.linkedin.com/in/jherre/" target="_blank">Connect with Jamie on LinkedIn</a>

## Upcoming events {#upcoming-events}

### Global virtual events {#global-virtual-events}

* <a href="https://clickhouse.com/company/events/v26-3-community-release-call" target="_blank">v26.3 Community Call</a> - Mar 26, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Korea-Webinar-CDC-ClickPipes" target="_blank">CDC ClickPipes: 데이터베이스를 ClickHouse로 복제하는 가장 빠른 방법</a> - Apr 1, 2026
* <a href="https://clickhouse.com/company/events/202603-AMER-Webinar-MaterializedViews" target="_blank">Under-the-Hood: ClickHouse Incremental Materialized Views and Dictionaries</a> - Apr 9, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres" target="_blank">Combining Postgres & ClickHouse to Build a Unified Data Stack</a> - Apr 22, 2026

### Virtual training {#virtual-training}

* <a href="https://clickhouse.com/company/events/202603-AMER-EMEA-Observabiity-with-ClickStackLevel3" target="_blank">Observability with ClickStack: Level 3</a> - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/202604-AMER-EMEA-Observabiity-with-ClickStackLevel2" target="_blank">Observability with ClickStack: Level 2</a> - Apr 7, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-query-optimization" target="_blank">Query Optimization with ClickHouse Workshop</a> - Apr 7, 2026

### Events in AMER {#events-in-amer}

* <a href="https://clickhouse.com/company/events/20250325" target="_blank">SRECon</a> - Seattle - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/20260324RSA" target="_blank">Bay Area Iceberg Meetup: RSA Edition</a> - San Francisco - Mar 24, 2026
* <a href="https://clickhouse.com/company/events/20260326OBS" target="_blank">Seattle Observability Meetup</a> - Seattle - Mar 26, 2026
* <a href="https://clickhouse.com/company/events/20260331SF" target="_blank">Fireside Chat in San Francisco: Column Stores & the Evolution of Observability</a> - San Francisco - Mar 31, 2026
* <a href="https://clickhouse.com/company/events/20260401Start" target="_blank">Seattle Startup Summit</a> - Seattle - Apr 1, 2026
* <a href="https://clickhouse.com/company/events/2026048ICEY" target="_blank">Iceberg Summit</a> - San Francisco - Apr 8, 2026
* <a href="https://clickhouse.com/company/events/20260409SF" target="_blank">AI Demo Night</a> - San Francisco - Apr 9, 2026
* <a href="https://clickhouse.com/company/events/google-cloud-next-2026" target="_blank">Google Cloud Next 2026</a> - Las Vegas - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/2026-houseparty-google-next" target="_blank">House Party, Google Cloud Next</a> - Las Vegas - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/202605-global-open-house" target="_blank">Open House</a> - San Francisco - May 26, 2026

### Events in EMEA {#events-in-emea}

* <a href="https://clickhouse.com/company/events/202603-EMEA-Munich-meetup" target="_blank">ClickHouse Meetup Munich</a> - Munich - Mar 19, 2026
* <a href="https://talk.clickhouse.com/MjM4LUZQQy0zMTcAAAGgUzZGmrCsh9LwlOhfQ5S4NuJ8xeCq3mUb8G5BcC77kIWCSLiIIgxcRMEPPICfZJY68i42uok=" target="_blank">KubeCon EU</a> - Amsterdam, 23-26 March - Hall 2 stand 261
* <a href="https://talk.clickhouse.com/MjM4LUZQQy0zMTcAAAGgUzZGmmaesRMWlmOCJFDfP-dRMIF62KX7wSNwW7XuKDPuAZhovJEFU7vmLZKWfQhES_NXQvg=" target="_blank">DW&BI</a> - Utrecht, 24 March
* <a href="https://clickhouse.com/company/events/202603-EMEA-Milan-meetup" target="_blank">ClickHouse x Apache Kafka x AWS</a> - Milan - Mar 26, 2026
* <a href="https://ti.to/maltaawsusergroup/gen-a-i-day-malta/with/lpqxlepi9ww" target="_blank">ClickHouse x AWS x DoiT - Gaming and Betting AI Workshop</a> - Malta - March 26, 2026
* <a href="https://clickhouse.com/company/events/202603-EMEA-Benelux-Amsterdam-LunchandLearn" target="_blank">Lunch & Learn with ClickHouse</a> - Amsterdam - Mar 31, 2026
* <a href="https://aws.amazon.com/events/summits/paris/" target="_blank">AWS Summit Paris</a> - Paris, 1 April - Level 1 (nr. G3)
* <a href="https://clickhouse.com/company/events/202604-EMEA-Observability-with-ClickStack" target="_blank">Amsterdam In-Person Training: Observability with ClickStack</a> - Amsterdam - Apr 8, 2026
* <a href="https://events.confluent.io/awsstartupexchangevienna2026" target="_blank">CTO Networking Event with ClickHouse, AWS, Confluent & DoiT</a> - Vienna - Apr 9, 2026
* <a href="https://luma.com/wjv3v1tn" target="_blank">Scaling the Enterprise AI Operating Model</a> - Berlin - Apr 14, 2026
* <a href="https://clickhouse.com/company/events/202604-EMEA-Paris-Real-time-Analytics-w-ClickHouse" target="_blank">Paris In-Person Training: Real-time Analytics with ClickHouse</a> - Paris - Apr 15, 2026
* <a href="https://www.meetup.com/clickhouse-ireland-user-group/events/313793261" target="_blank">ClickHouse Meetup in Dublin</a> - Dublin - Apr 16, 2026
* <a href="https://grafana.com/events/grafanacon/" target="_blank">GrafanaCON</a> - Barcelona - Apr 20-22, 2026
* <a href="https://clickhouse.com/company/events/202604-EMEA-Barcelona-Real-time-Analytics-w-ClickHouse" target="_blank">Barcelona In-Person Training: Real-time Analytics with ClickHouse</a> - Barcelona - Apr 20, 2026
* <a href="https://aws.amazon.com/events/summits/london/" target="_blank">AWS Summit London</a> - London - Apr 22 - Booth G18
* <a href="https://riseof.ai/conference-2026/" target="_blank">Rise of AI Berlin</a> - Berlin - May 5-6, 2026
* <a href="https://aws.amazon.com/events/summits/tel-aviv/" target="_blank">AWS Summit Tel Aviv</a> - Tel Aviv - May 6, 2026
* <a href="https://datainnovationsummit.com/" target="_blank">Data Innovation Summit</a> - Stockholm - May 6-8, 2026
* <a href="http://gartner.com/en/data-analytics" target="_blank">Gartner Data & Analytics</a> - London - May 11-13, 2026
* <a href="https://www.revolutionbanking.es/" target="_blank">Revolution Banking</a> - Madrid - May 12, 2026
* <a href="https://www.platfor-ma.com/" target="_blank">Platforma 2026</a> - Tel Aviv - May 20, 2026
* <a href="https://aws.amazon.com/events/summits/hamburg/" target="_blank">AWS Summit Hamburg</a> - Hamburg - May 20, 2026
* <a href="https://clickhouse.com/company/events/202605-EMEA-London-Real-time-Analytics-w-ClickHouse" target="_blank">London 2-day In-Person Training: Real-time Analytics with ClickHouse</a> - London - May 19, 2026
* <a href="https://www.meetup.com/clickhouse-london-user-group/events/313759007/" target="_blank">ClickHouse Meetup London</a> - London - May 19, 2026
* <a href="https://cloudonair.withgoogle.com/events/cloud-ai-live-madrid-2026" target="_blank">Google Summit Madrid</a> - Madrid - May 28, 2026

### Events in APAC {#events-in-apac}

* <a href="https://2026.pythonasia.org/" target="_blank">Python Asia 2026</a> - Manila - Mar 21-22, 2026
* <a href="https://clickhouse.com/company/events/meetup-nz-23mar2026" target="_blank">Postgres + ClickHouse: Building a Real-Time Open-Source Data Stack</a> - Auckland - Mar 23, 2026
* <a href="https://dataengbytes.com/2026/auckland" target="_blank">DataEngBytes Auckland</a> - Mar 24, 2026
* <a href="https://clickhouse.com/company/events/meetup-nz-25mar2026" target="_blank">Wellington Data Eng Meetup</a> - Wellington - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/202603-apj-shenzhen-meetup" target="_blank">ClickHouse Shenzhen Meetup</a> - Shenzhen - Mar 28, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Korea-Webinar-CDC-ClickPipes" target="_blank">Korean Webinar: CDC ClickPipes: 데이터베이스를 ClickHouse로 복제하는 가장 빠른 방법</a> - Apr 1, 2026
* <a href="https://events.confluent.io/dswt2026mumbai" target="_blank">Data Streaming World Mumbai</a> - Apr 13, 2026
* <a href="https://events.confluent.io/dswt2026bangalore" target="_blank">Data Streaming World Bangalore</a> - Apr 16, 2026
* <a href="https://clickhouse.com/company/events/taipei-open-source-meetup" target="_blank">Taipei Open Source Meetup</a> - Taipei - Apr 16, 2026
* <a href="https://www.meetup.com/clickhouse-bangalore-user-group/events/313739871/" target="_blank">Bangalore Meetup with Alexey Milovidov</a> - Apr 18, 2026
* <a href="https://events.confluent.io/dswt2026jakarta" target="_blank">Data Streaming World Jakarta</a> - Apr 21, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres" target="_blank">APJ Webinar: Combining Postgres & ClickHouse to Build a Unified Data Stack</a> - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-HoChiMinh-Real-time-Analytics-with-ClickHouse" target="_blank">Ho Chi Minh In-Person Training: Real-time Analytics with ClickHouse</a> - Ho Chi Minh - Apr 22, 2026
* <a href="https://aws.amazon.com/events/summits/bengaluru/" target="_blank">AWS Summit Bengaluru</a> - Apr 22-23, 2026

## 26.2 release {#26-2-release}

![](/uploads/mar2026_nl_image9_00d00cbf75.png)

My favorite feature in the recent ClickHouse 26.2 release is time-based block flushing for streaming data. This lets you batch inserts by time interval rather than row count, which is useful for low-throughput feeds like Wikimedia recent changes.

The release also brings production-ready text index and QBit data types, 3.2x faster RIGHT/FULL JOINs, and embedded ClickStack for in-product observability.

➡️ <a href="https://clickhouse.com/blog/clickhouse-release-26-02" target="_blank">Read the release post</a>

## Building towards an enterprise-grade Postgres service in ClickHouse Cloud {#building-towards-an-enterprise-grade-postgres-service}

![](/uploads/mar2026_nl_image3_b4e4cebe10.jpg)

Sai Srirampur introduces the enterprise-grade Postgres service, coming soon to ClickHouse Cloud, bringing cross-AZ high availability, point-in-time recovery, automated backups, and failover-safe CDC slots for ClickHouse integration.

In other Postgres news, Kaushik Iska introduces <a href="https://clickhouse.com/blog/pg_stat_ch-postgres-extension-stats-to-clickhouse" target="_blank">pg_stat_ch</a>, an open-source Postgres extension that streams query metrics directly into ClickHouse for latency analysis and error tracking without impacting production performance.

➡️ <a href="https://clickhouse.com/blog/enterprise-postgres-service-in-clickhouse-cloud" target="_blank">Read the blog post</a>

## Announcing chDB 4: Write Pandas, Run ClickHouse, Now on Hex {#announcing-chdb-4}

![](/uploads/mar2026_nl_image10_d4f00e1d08.png)

Ryadh Dahimene and Auxten Wang introduce chDB 4, which adds a Pandas-compatible DataStore API that executes on ClickHouse's engine under the hood.

Operations run lazily as an optimized pipeline, with automatic routing between ClickHouse and Pandas engines, and it's now available natively in Hex notebooks.

➡️ <a href="https://clickhouse.com/blog/chdb.4-0-pandas-hex" target="_blank">Read the release post</a>

## How Trigger.dev built a custom SQL language on top of ClickHouse {#how-trigger-dev-built-a-custom-sql-language}

![](/uploads/mar2026_nl_image6_3d154dfd7b.png)

Matt Aitken, CEO of Trigger.dev, explains how they gave users SQL access to a shared multi-tenant ClickHouse cluster without risking data leaks.

Their solution is TRQL, a SQL-style DSL that compiles to tenant-isolated ClickHouse queries - dangerous operations are grammatically impossible, and tenant filters are injected at compile time.

➡️ <a href="https://trigger.dev/blog/how-trql-works" target="_blank">Read the blog post</a>

## Announcing General Availability of ClickHouse Full-text Search {#announcing-general-availability-of-full-text-search}

![](/uploads/mar2026_nl_image7_a3a43dd469.png)

Melvyn Peignon announces the general availability of full-text search in ClickHouse, which uses native inverted indexes to enable fast token-based filtering at scale.

The implementation supersedes Bloom filters for string matching, delivering deterministic results without false positives and reducing the number of granules scanned by up to 96%.

To see it in action, Lionel Palacin built <a href="https://clickhouse.com/blog/gittrends" target="_blank">GitTrends</a>, an open-source demo that searches and aggregates nearly 10 billion GitHub events in real time, with a live comparison tool showing the performance differences between full-text search, Bloom filters, and a full table scan.

➡️ <a href="https://clickhouse.com/blog/full-text-search-ga-release" target="_blank">Read the blog post</a>

## How we made payload search 60x faster in ClickHouse {#how-we-made-payload-search-60x-faster}

![](/uploads/mar2026_nl_image5_7eaefa6d9a.png)

Maurice Kherlakian at Hookdeck describes how webhook payload search across millions of semi-structured JSON records was timing out at 30+ seconds, making debugging nearly impossible.

The fix: hashing values into typed bucket columns so queries scan a single bucket instead of all, combined with iterative time-window scanning that stops once enough results are found, bringing latency down to under 400ms.

➡️ <a href="https://hookdeck.com/blog/how-we-made-payload-search-60x-faster-in-clickhouse" target="_blank">Read the blog post</a>

## The Agentic Data Stack {#the-agentic-data-stack}

![](/uploads/mar2026_nl_image1_9d8086f2d3.png)

Dustin Healy outlines an open-source agentic data stack that lets AI agents query ClickHouse directly via natural language, replacing dashboards and data tickets with real-time conversational access.

The architecture combines ClickHouse's MCP server with an open-source LLM interface and Langfuse for observability, keeping data and infrastructure under the user's control.

➡️ <a href="https://clickhouse.com/blog/the-agentic-data-stack" target="_blank">Read the blog post</a>

## ClickHouse TTL in production: A safe strategy for data retention and disk optimization {#clickhouse-ttl-in-production}

![](/uploads/mar2026_nl_image8_5f21853f75.png)

Aliakbar Hosseinzadeh shares a production runbook for implementing ClickHouse TTL policies after his cluster hit 97% disk utilization, covering the key mental model shift: TTL runs during background merges, not at insert time.

The winning combination is to align partitioning with your TTL time unit and `set ttl_only_drop_parts=1`, which lets ClickHouse drop whole parts cleanly rather than triggering expensive mutation-style rewrites.

➡️ <a href="https://medium.com/@aliakbarhosseinzadeh/clickhouse-ttl-in-production-a-safe-strategy-for-data-retention-and-disk-optimization-9f1546fe673f" target="_blank">Read the blog post</a>

## Quick reads {#quick-reads}

* Mark Needham <a href="https://clickhouse.com/blog/state-of-geospatial-march-2026" target="_blank">surveys everything ClickHouse can do with geospatial data in 2026</a> - from Geometry types and spatial operations to H3 grid-based analytics that run 12x faster than bounding-box queries on 10 million rows.
* Fiona J. Sylvester shows how to run <a href="https://medium.com/@fiona.j.sylvester/wait-you-can-do-anomaly-detection-directly-inside-a-database-ba6ec8dceb8c" target="_blank">STL-based anomaly detection directly in ClickHouse</a>, catching subtle deviations like a silent 4% transaction drop that would be missed by fixed thresholds.
* Parade suggests that <a href="https://medium.com/@parade4940/stop-calling-it-saas-why-you-should-dump-microsoft-fabric-and-use-clickhouse-for-data-analytics-62e73b3cffbe" target="_blank">Microsoft Fabric's shared capacity model creates unpredictable performance</a> and runaway costs, and that swapping in ClickHouse as the analytics engine keeps Power BI intact while cutting bills from tens to thousands of dollars a month.
* Tom Schreiber <a href="https://clickhouse.com/blog/table-cloning" target="_blank">explains how the CLONE AS command</a> creates instant copies of tables of any size by hard-linking immutable data parts rather than copying bytes, enabling safe experimentation on production-scale data with near-zero storage overhead.
* Shuva Jyoti Kar <a href="https://medium.com/google-cloud/agentic-threat-hunting-conversational-telemetry-with-clickhouse-the-new-mcp-java-sdk-and-google-bb3d98aff1f3" target="_blank">built a SecOps agent</a> that uses Google's MCP Toolbox and ClickHouse to let analysts query millions of rows of security telemetry in plain English, without exposing the schema or letting the LLM write raw SQL.
* Mohamed Hussain S explains how <a href="https://medium.com/@mohhddhassan/the-clickhouse-mental-model-most-engineers-miss-c1f39b18f46f" target="_blank">understanding two ClickHouse internals</a> - aggregation states and argMax - unlocks simpler, more powerful query design that would require subqueries and joins in traditional SQL databases.


Welcome to the March 2026 ClickHouse newsletter, which will round up what’s happened in real-time data warehouses over the last month.

large_Newsletter Cover Facebook Post (18).png

medium_Newsletter Cover Facebook Post (18).png

small_Newsletter Cover Facebook Post (18).png

thumbnail_Newsletter Cover Facebook Post (18).png

Newsletter Cover Facebook Post (18).png

March 2026 newsletter

Dates come in all shapes and sizes - Unix timestamps from event streams, weird looking numeric dates from legacy database exports, ISO 8601 strings from APIs, and more. Lucky for us, ClickHouse has a rich set of functions to handle all of them and that's what we're going to explore in this blog post.

We'll start with the most explicit approaches: converting Unix timestamps with `fromUnixTimestamp`, parsing packed numeric dates with `YYYYMMDDToDate`, and parsing known format strings with `parseDateTime`. Then we'll look at the `parseDateTimeBestEffort` family for when the format is unknown or mixed. 

Finally, we'll cover how casting dates with the `cast_string_to_date_time_mode` setting might be a better choice than explicit function calls for some use cases.

<iframe width="768" height="432" src="https://www.youtube.com/embed/hVEAzVz_xIY?si=-8QknStXsAO9iMjI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Unix timestamps {#unix_timestamps}

First up, Unix timestamps! Unix timestamps represent the number of seconds since January 1st, 1970. We can use the [`fromUnixTimestamp`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#fromUnixTimestamp) function to convert them:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp(1704067295) AS val1, toTypeName(val1);
</code></pre>

This returns a `DateTime` type. If you have milliseconds since January 1st, 1970, there's a different function — [`fromUnixTimestamp64Milli`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#fromUnixTimestamp64Milli) — and the type comes back as `DateTime64(3)`, where the `3` means precision up to milliseconds. 

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp64Milli(1704067295123) AS val2, toTypeName(val2);
</code></pre>

For microseconds, [`fromUnixTimestamp64Micro`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#fromUnixTimestamp64Micro) returns `DateTime64(6)`:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp64Micro(1704067295123456) AS val3, toTypeName(val3);
</code></pre>

## Numeric date formats {#numeric_date_formats}

Sometimes dates are represented as plain numbers encoding the year, month, and day — with no separators or formatting. This is common in legacy database exports or flat files from mainframes. The function [`YYYYMMDDToDate`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#YYYYMMDDToDate) handles this:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    YYYYMMDDToDate(20240115) AS val1, toTypeName(val1);
</code></pre>

If the number also includes time information, [`YYYYMMDDhhmmssToDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#YYYYMMDDhhmmssToDateTime) handles that too:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    YYYYMMDDhhmmssToDateTime(20240115143022) AS val2, toTypeName(val2);
</code></pre>

## Known format strings {#known_format_strings}

APIs often return dates as strings. If you know the format, you can use [`parseDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime) with a MySQL date format string:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    parseDateTime('15/01/2024 14:30:22', '%d/%m/%Y %H:%i:%s') AS val1,
    toTypeName(val1);
</code></pre>

This returns a `DateTime` including the timezone. 

If you prefer Joda date format strings, there's [`parseDateTimeInJodaSyntax`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTimeInJodaSyntax) which produces the same output:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    parseDateTimeInJodaSyntax('15/01/2024 14:30:22', 'dd/MM/yyyy HH:mm:ss') AS val2,
    toTypeName(val2);
</code></pre>

## Best effort parsing of DateTimes {#best_effort_parsing}

The previous three approaches all assumed we knew the exact date format. But what if we don't? That's where the [`parseDateTimeBestEffort`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTimeBestEffort) family of functions comes in. Imagine we have dates in a mix of different formats:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, parseDateTimeBestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

We can also convert to `DateTime64` using [`parseDateTimeBestEffort64`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffort), like the earlier functions:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

What happens if we include a completely invalid date? 

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

ClickHouse throws an exception! 

We can work around this with the [`parseDateTimeBestEffort64OrNull`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffortOrNull) variant, which returns `NULL` instead:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffortOrNull(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

Or if you'd rather get an actual datetime value, [`parseDateTimeBestEffort64OrZero`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffortOrZero) falls back to January 1st, 1970 at midnight:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffortOrZero(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

## Casting {#casting}

If you'd rather avoid calling explicit parse functions throughout your queries, you can cast string values directly to date types using `::DateTime`. However, there's an important setting to be aware of: `cast_string_to_date_time_mode`.

By default it's set to `basic`, which handles standard formats like `YYYY-MM-DD` and `YYYY-MM-DD HH:MM:SS`, but anything else will fail. For broader format support, change it to `best_effort`. Note that this setting still throws an exception for completely invalid dates.

You can pass the setting inline per query:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM dates
SETTINGS cast_string_to_date_time_mode = 'best_effort';
</code></pre>

Or configure it at the session level so you don't need it in every query:

<pre><code type='click-ui' language='sql'>
SET cast_string_to_date_time_mode = 'best_effort';
</code></pre>

Then the same query works without the `SETTINGS` clause:

<pre><code type='click-ui' language='sql'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM dates;
</code></pre>

Finally, imagine that we have the following file that contains a variety of dates:

*dates.csv*
```csv
raw
2024-01-15T14:30:22.000Z
2024-01-15
1704067295
```

We can parse the dates in that file using the same approach:

<pre><code type='click-ui' language='sql'>
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM file('dates.csv', CSVWithNames);
</code></pre>

```shell
┌─raw──────────────────────┬─────────────────val─┬─toTypeName(val)─┐
│ 2024-01-15T14:30:22.000Z │ 2024-01-15 14:30:22 │ DateTime        │
│ 2024-01-15               │ 2024-01-15 00:00:00 │ DateTime        │
│ 1704067295               │ 2024-01-01 00:01:35 │ DateTime        │
└──────────────────────────┴─────────────────────┴─────────────────┘
```

5 Ways to Parse Dates and DateTimes in ClickHouse

large_Image 1920x1080.jpg

medium_Image 1920x1080.jpg

small_Image 1920x1080.jpg

thumbnail_Image 1920x1080.jpg

Image 1920x1080.jpg

5 ways to parse Dates and DateTimes in ClickHouse

Linux, macOS, FreeBSD

A data lakehouse is a data architecture that combines the low-cost, open storage of a data lake with the performance and governance of a data warehouse. It stores data in open formats on cloud object storage, so the same dataset can serve BI, large-scale analytics, and AI/ML workloads without duplication or movement.

The architecture emerged as organizations ran into the limits of running a data lake and a data warehouse in parallel. Lakes gave them cheap, flexible storage but poor query performance and weak governance. Warehouses gave them structure and speed but at a premium price, on proprietary formats, with tightly coupled compute. The lakehouse applies database principles — schema enforcement, ACID transactions, metadata management — directly to the open files in object storage, which removes the need to copy data between two systems to support both sets of workloads.

Mark Needham

Blog posts by Mark Needham

Comparing ClickHouse versions with clickhousectl

April 2026 newsletter

Index-based pruning in ClickHouse

Querying DateTimes in ClickHouse

March 2026 newsletter

5 ways to parse Dates and DateTimes in ClickHouse

Resources by Mark Needham

What is a data lakehouse?

What is Real-Time Analytics? A Complete Guide (2026)