---
title: "How to extract ISO week numbers from dates in ClickHouse®"
excerpt: "Learn how to extract ISO week numbers in ClickHouse® using toISOWeek function with syntax examples, performance tips, and edge case handling for accurate reporting."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-30 17:25:23"
publishedOn: "2025-10-02 17:25:23"
updatedOn: "2025-10-02 17:25:23"
status: "published"
---

ISO week numbers are important for calculating analytics at year boundaries. They let you avoid partial-week aggregates at those boundaries. Getting the ISO week number from dates in ClickHouse{% sup %}®{% /sup %} trips up more developers than you'd expect, especially when December 31st returns week 1 of the following year. The `toISOWeek()` function follows the ISO 8601 standard where weeks start on Monday and week 1 contains January 4th, creating some counterintuitive results around year boundaries.

This guide covers the `toISOWeek()` function syntax, input data types, performance optimization strategies, and how to handle the edge cases that catch most developers off guard when building time-based analytics.

## What is the ISO week number in ClickHouse{% sup %}®{% /sup %}

The ISO week number in ClickHouse{% sup %}®{% /sup %} represents the week of the year according to the ISO 8601 standard. This standard defines Monday as the first day of the week and establishes that week 1 is the first week containing January 4th.

Here's where it gets interesting—and where most developers get tripped up. December 31st might belong to week 1 of the following year, while January 1st could be part of week 52 or 53 of the previous year. This happens because the ISO standard prioritizes keeping weeks intact rather than forcing them to fit neatly within calendar year boundaries.

You'll encounter this mainly when building reports that need to be consistent across different regions and systems. Financial quarters, standardized business reporting, and any analytics that span multiple time zones benefit from this approach because everyone's using the same week numbering rules.

## toISOWeek function syntax and return type

ClickHouse{% sup %}®{% /sup %} provides the `toISOWeek()` function to extract ISO week numbers from dates. The function takes a date or datetime value and returns a UInt8 number between 1 and 53. It's been available since ClickHouse{% sup %}®{% /sup %} version 19.15, so you won't find it in older installations.

The basic syntax looks like this:

```sql
SELECT toISOWeek(date_column) FROM table_name;
```

### Sample query

Here's how you'd use it with a specific date:

```sql
SELECT toISOWeek('2023-12-31') AS week_number;
-- Returns: 52
```

You can also apply it to entire columns in your tables:

```sql
SELECT
    order_date,
    toISOWeek(order_date) AS iso_week
FROM orders
WHERE order_date >= '2024-01-01';
```

### Expected output

The function produces some surprising results around year boundaries. December 31st often returns week 1 of the following ISO year, while January 1st might return week 52 or 53 of the previous year.

```sql
SELECT
    date,
    toISOWeek(date) AS iso_week,
    toISOYear(date) AS iso_year
FROM (
    SELECT arrayJoin(['2023-12-31', '2024-01-01', '2024-01-07']) AS date
);
```

| date       | iso_week | iso_year |
|------------|----------|----------|
| 2023-12-31 | 52       | 2023     |
| 2024-01-01 | 1        | 2024     |
| 2024-01-07 | 1        | 2024     |

## Input data types supported by toISOWeek

The `toISOWeek()` function works with various date and time data types in ClickHouse{% sup %}®{% /sup %}. Understanding which types work directly and which need conversion saves you debugging time later.

### Date and Date32

Standard [Date](https://tinybird.co/docs/sql-reference/data-types/date) columns work directly with `toISOWeek()` without any conversion:

```sql
SELECT toISOWeek(toDate('2024-03-15')) AS week_number;
-- Returns: 11
```

[Date32](https://tinybird.co/docs/sql-reference/data-types/date32) columns also work seamlessly. Date32 supports an extended range from 1900 to 2299, which comes in handy for historical data analysis or long-term projections.

### DateTime and DateTime64

The function extracts the date portion from timestamp columns automatically, ignoring the time component entirely:

```sql
SELECT
    toISOWeek(now()) AS current_week,
    toISOWeek(toDateTime('2024-06-15 14:30:00')) AS specific_week;
```

Time zone handling depends on your server configuration and column definitions. [DateTime](https://tinybird.co/docs/sql-reference/data-types/datetime) columns with explicit time zones convert to the server's time zone before calculating the week number, which can shift the result for timestamps near midnight.

### Parsing ISO dates from strings

String values require conversion to date types before applying `toISOWeek()`. Use `parseDateTime()` or `toDate()` depending on your string format:

```sql
SELECT
    toISOWeek(toDate('2024-08-20')) AS from_date_string,
    toISOWeek(parseDateTime('2024-08-20 10:30:00', '%Y-%m-%d %H:%M:%S')) AS from_datetime_string;
```

Common patterns include YYYY-MM-DD for dates and various ISO 8601 datetime formats that `parseDateTime()` handles with appropriate format strings.

## toISOWeek vs toWeek mode 3

Both `toISOWeek(date)` and `toWeek(date, 3)` produce identical results—they're two different ways to calculate the same ISO week number. The key difference lies in syntax clarity and code readability.

```sql
-- These queries return identical results
SELECT
    toISOWeek('2024-03-15') AS iso_week_method,
    toWeek('2024-03-15', 3) AS week_mode_3_method;
```

| Function | Syntax | Best For |
| -------- | ------ | -------- |
| `toISOWeek(date)` | Single parameter | New projects, explicit ISO compliance |
| `toWeek(date, 3)` | Date + mode parameter | Legacy code, mixing different week modes |

### When to use toISOWeek

Choose `toISOWeek()` for new projects where you want explicit ISO compliance and cleaner, more readable code. The function name makes your intent clear to other developers reading your queries.

### When to use toWeek

Use `toWeek(date, 3)` when working with legacy codebases or when you need to mix different week calculation modes in the same query. The `toWeek()` function supports modes 0-9 for various week numbering systems.

### Migration checklist

Replacing `toWeek(date, 3)` with `toISOWeek(date)` is straightforward, but validation ensures accuracy:

```sql
-- Validation query to confirm identical results
SELECT
    date_column,
    toWeek(date_column, 3) AS old_method,
    toISOWeek(date_column) AS new_method,
    toWeek(date_column, 3) = toISOWeek(date_column) AS results_match
FROM your_table
WHERE results_match = 0; -- Returns no rows if methods are identical
```

## Handling year boundaries and time zones

ISO week numbering creates edge cases that can confuse developers, especially around New Year's Day and when working with different time zones. Understanding these behaviors prevents incorrect aggregations and reporting errors.

### December–January crossover

The most common surprise occurs when December dates belong to the following year's first week, or January dates belong to the previous year's final week:

```sql
SELECT
    date,
    toISOWeek(date) AS iso_week,
    toISOYear(date) AS iso_year
FROM (
    SELECT arrayJoin([
        '2022-12-31', -- Week 52 of 2022
        '2024-01-01', -- Week 1 of 2024
        '2025-12-29'  -- Week 1 of 2026
    ]) AS date
);
```

December 29, 2025 returns week 1 because it falls in the first week that contains January 4, 2026. This behavior follows ISO 8601 exactly but often catches developers off guard.

### Europe vs UTC offsets

Time zone differences can shift which calendar day gets processed, affecting the week calculation:

```sql
SELECT
    toISOWeek(toTimeZone(toDateTime('2024-01-01 02:00:00'), 'UTC')) AS utc_week,
    toISOWeek(toTimeZone(toDateTime('2024-01-01 02:00:00'), 'Europe/London')) AS london_week;
```

Use `toTimeZone()` when you need consistent week calculations across different geographic regions or when your data spans multiple time zones.

### Nullable or missing dates

Handle NULL values explicitly to prevent unexpected results in aggregations:

```sql
SELECT
    CASE
        WHEN isNull(date_column) THEN NULL
        ELSE toISOWeek(date_column)
    END AS safe_iso_week
FROM table_with_nullable_dates;
```

For columns where you know NULLs represent specific default dates, `assumeNotNull()` can improve performance by avoiding NULL checks.

## Performance tips for large tables

When processing millions of date records, optimizing ISO week calculations can significantly improve query performance and reduce resource usage.

### Materialized UInt8 column

Pre-computing ISO week numbers as materialized columns eliminates repeated function calls during queries:

```sql
ALTER TABLE orders
ADD COLUMN iso_week UInt8 MATERIALIZED toISOWeek(order_date);
```

This approach trades storage space for query speed—each row stores an additional byte, but week-based aggregations become much faster since the calculation happens only once during insertion.

### Using a secondary index

Adding a minmax index on the ISO week column accelerates filtering operations:

```sql
ALTER TABLE orders
ADD INDEX idx_iso_week iso_week TYPE minmax GRANULARITY 4;
```

The minmax index works well for week numbers because they have a limited range (1-53) and often exhibit locality in time-series data.

### Back-filling historical data

After adding a materialized column, populate existing rows with an UPDATE statement:

```sql
ALTER TABLE orders
UPDATE iso_week = toISOWeek(order_date)
WHERE iso_week = 0; -- Only update rows where the column wasn't populated
```

Run `OPTIMIZE TABLE orders` after large UPDATE operations to merge parts and improve subsequent query performance.

## Step-by-step: add an ISO week column

Here's a practical walkthrough for adding ISO week tracking to an existing table with date-based data.

### 1. Alter table to add column

Add the materialized column that automatically calculates ISO week numbers for new insertions:

```sql
ALTER TABLE sales_data
ADD COLUMN iso_week UInt8 MATERIALIZED toISOWeek(sale_date);
```

The MATERIALIZED keyword ensures that ClickHouse{% sup %}®{% /sup %} automatically populates this column for all new rows without requiring changes to your INSERT statements.

### 2. Populate column with UPDATE

Fill the column for existing historical data using an UPDATE statement:

```sql
ALTER TABLE sales_data
UPDATE iso_week = toISOWeek(sale_date)
WHERE sale_date < today() AND iso_week = 0;
```

Adding a WHERE condition improves performance on large tables by only processing rows that haven't been updated yet.

### 3. Validate results

Verify that week numbers match the ISO standard by checking edge cases:

```sql
SELECT
    sale_date,
    iso_week,
    toISOYear(sale_date) AS iso_year
FROM sales_data
WHERE sale_date IN ('2023-12-31', '2024-01-01', '2024-01-07')
ORDER BY sale_date;
```

Look for the characteristic year-boundary behavior where December 31st might show week 1 of the following year.

## Building ClickHouse{% sup %}®{% /sup %}-based analytics APIs with ISO week numbers using Tinybird

A common business analytics pattern built with ClickHouse{% sup %}®{% /sup %} involves tracking weekly performance metrics using ISO week standards for consistent reporting across different regions and time zones. Using `toISOWeek()`, you can build APIs that properly handle year-boundary edge cases and provide reliable week-over-week comparisons.

Here's how to build a ClickHouse{% sup %}®{% /sup %}-based sales analytics API using Tinybird's [managed ClickHouse{% sup %}®{% /sup %} platform](https://www.tinybird.co/clickhouse).

### Step 1: Create a data source for sales transactions

First, create a [data source](https://www.tinybird.co/docs/forward/get-data-in/data-sources) to store your sales data with proper ISO week calculation. Create a `sales_transactions.datasource` file:

```tinybird
SCHEMA >
    `transaction_id` String `json:$.transaction_id`,
    `sale_date` Date `json:$.sale_date`,
    `customer_id` String `json:$.customer_id`,
    `product_id` String `json:$.product_id`,
    `product_category` LowCardinality(String) `json:$.product_category`,
    `sales_amount` Float64 `json:$.sales_amount`,
    `quantity` UInt32 `json:$.quantity`,
    `sales_rep_id` String `json:$.sales_rep_id`,
    `region` LowCardinality(String) `json:$.region`,
    `iso_week` UInt8 MATERIALIZED toISOWeek(sale_date),
    `iso_year` UInt16 MATERIALIZED toISOYear(sale_date)

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(sale_date)"
ENGINE_SORTING_KEY "sale_date, customer_id"
```

### Step 2: Test locally with sample data

Start the local Tinybird development server:

```bash
tb dev
```

This starts a local Tinybird instance at `http://localhost:7181`. Your data sources are automatically deployed to the local environment.

Ingest sample sales data using the local [Events API](https://www.tinybird.co/docs/forward/get-data-in/events-api):

```bash
curl -X POST \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  "http://localhost:7181/v0/events?name=sales_transactions" \
  -d '[
    {"transaction_id": "txn_001", "sale_date": "2024-12-30", "customer_id": "cust_123", "product_id": "prod_456", "product_category": "Electronics", "sales_amount": 1299.99, "quantity": 1, "sales_rep_id": "rep_001", "region": "North"},
    {"transaction_id": "txn_002", "sale_date": "2024-12-31", "customer_id": "cust_124", "product_id": "prod_457", "product_category": "Clothing", "sales_amount": 89.50, "quantity": 2, "sales_rep_id": "rep_002", "region": "South"},
    {"transaction_id": "txn_003", "sale_date": "2025-01-01", "customer_id": "cust_125", "product_id": "prod_458", "product_category": "Electronics", "sales_amount": 599.99, "quantity": 1, "sales_rep_id": "rep_001", "region": "East"},
    {"transaction_id": "txn_004", "sale_date": "2025-01-06", "customer_id": "cust_126", "product_id": "prod_459", "product_category": "Home", "sales_amount": 199.99, "quantity": 3, "sales_rep_id": "rep_003", "region": "West"}
  ]'
```

### Step 3: Create weekly analytics APIs

Create a pipe for weekly sales performance analysis. Create `weekly_sales_analytics.pipe`:

```tinybird
NODE weekly_sales_analysis
SQL >
    %
    SELECT
        iso_year,
        iso_week,
        -- Week boundaries for reference
        toMonday(sale_date) AS week_start,
        toSunday(sale_date) AS week_end,
        product_category,
        region,
        -- Sales metrics
        count() AS transaction_count,
        sum(sales_amount) AS total_sales,
        avg(sales_amount) AS avg_transaction_value,
        sum(quantity) AS total_quantity,
        uniq(customer_id) AS unique_customers,
        uniq(sales_rep_id) AS active_sales_reps,
        -- Performance indicators
        round(sum(sales_amount) / 7, 2) AS daily_avg_sales
    FROM sales_transactions
    WHERE sale_date >= {{Date(start_date, '2024-12-01')}}
      AND sale_date <= {{Date(end_date, '2025-01-31')}}
    {\% if defined(category_filter) %}
      AND product_category = {{String(category_filter)}}
    {\% end %}
    {\% if defined(region_filter) %}
      AND region = {{String(region_filter)}}
    {\% end %}
    {\% if defined(iso_year_filter) %}
      AND iso_year = {{UInt16(iso_year_filter)}}
    {\% end %}
    {\% if defined(min_week) %}
      AND iso_week >= {{UInt8(min_week, 1)}}
    {\% end %}
    {\% if defined(max_week) %}
      AND iso_week <= {{UInt8(max_week, 53)}}
    {\% end %}
    GROUP BY
        iso_year,
        iso_week,
        week_start,
        week_end,
        product_category,
        region
    ORDER BY iso_year, iso_week, total_sales DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 100)}}
    {\% end %}

TYPE ENDPOINT
```

Create a year-boundary analysis API to demonstrate ISO week edge cases. Create `year_boundary_analysis.pipe`:

```tinybird
NODE boundary_analysis
SQL >
    %
    SELECT
        sale_date,
        iso_year,
        iso_week,
        toYear(sale_date) AS calendar_year,
        toWeek(sale_date) AS calendar_week,
        -- Highlight year boundary cases
        CASE
            WHEN toYear(sale_date) != iso_year THEN 'year_boundary_case'
            ELSE 'normal_case'
        END AS boundary_type,
        sum(sales_amount) AS daily_sales,
        count() AS daily_transactions,
        -- Week context
        concat('Week ', toString(iso_week), ' of ISO year ', toString(iso_year)) AS iso_week_label
    FROM sales_transactions
    WHERE sale_date >= {{Date(start_date, '2024-12-25')}}
      AND sale_date <= {{Date(end_date, '2025-01-10')}}
    GROUP BY
        sale_date,
        iso_year,
        iso_week,
        calendar_year,
        calendar_week,
        boundary_type,
        iso_week_label
    ORDER BY sale_date
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 50)}}
    {\% end %}

TYPE ENDPOINT
```

### Step 4: Test the APIs locally

Test your weekly analytics APIs locally:

```bash
# Test main weekly sales analytics
curl "http://localhost:7181/v0/pipes/weekly_sales_analytics.json?start_date=2024-12-01&end_date=2025-01-31"

# Filter by specific product category and region
curl "http://localhost:7181/v0/pipes/weekly_sales_analytics.json?category_filter=Electronics&region_filter=North"

# Analyze year boundary cases
curl "http://localhost:7181/v0/pipes/year_boundary_analysis.json?start_date=2024-12-25&end_date=2025-01-10"

# Focus on specific ISO year and week range
curl "http://localhost:7181/v0/pipes/weekly_sales_analytics.json?iso_year_filter=2024&min_week=50&max_week=53"
```

You should see the ISO week behavior in action - December 30-31, 2024 will show as week 1 of ISO year 2025, demonstrating the year boundary handling.

### Step 5: Deploy to production

Once you've tested your weekly analytics locally and verified the ISO week calculations work correctly, deploy to your Tinybird cloud workspace:

```bash
tb --cloud deploy
```

Your APIs are now available in production:

```bash
# Production API calls
curl "$TINYBIRD_HOST/v0/pipes/weekly_sales_analytics.json?start_date=2024-12-01&end_date=2025-01-31" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

curl "$TINYBIRD_HOST/v0/pipes/year_boundary_analysis.json?start_date=2024-12-25&end_date=2025-01-10" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"
```

The APIs return comprehensive weekly analytics with proper ISO week handling:

```json
{
    "meta": [
        {"name": "iso_year", "type": "UInt16"},
        {"name": "iso_week", "type": "UInt8"},
        {"name": "week_start", "type": "Date"},
        {"name": "week_end", "type": "Date"},
        {"name": "product_category", "type": "String"},
        {"name": "region", "type": "String"},
        {"name": "transaction_count", "type": "UInt64"},
        {"name": "total_sales", "type": "Float64"},
        {"name": "avg_transaction_value", "type": "Float64"},
        {"name": "unique_customers", "type": "UInt64"},
        {"name": "daily_avg_sales", "type": "Float64"}
    ],
    "data": [
        {
            "iso_year": 2025,
            "iso_week": 1,
            "week_start": "2024-12-30",
            "week_end": "2025-01-05",
            "product_category": "Electronics",
            "region": "North",
            "transaction_count": 2,
            "total_sales": 1899.98,
            "avg_transaction_value": 949.99,
            "unique_customers": 2,
            "daily_avg_sales": 271.43
        }
    ],
    "rows": 1,
    "statistics": {
        "elapsed": 0.004,
        "rows_read": 4,
        "bytes_read": 512
    }
}
```

This approach provides:

- **Proper ISO week calculation** with materialized columns for optimal performance
- **Year boundary handling** that correctly assigns December 30-31, 2024 to ISO week 1 of 2025
- **Comprehensive filtering** by date ranges, categories, regions, and specific ISO years/weeks
- **Business-relevant metrics** including daily averages and customer analysis
- **Edge case demonstration** through the year boundary analysis API

With Tinybird, your time series analytics metrics become available as production-ready APIs that automatically handle ISO week complexities while providing accurate, real-time data. [Start building with Tinybird's free plan](https://cloud.tinybird.co/signup) to create your own time series analytics APIs.

## What to do next with ClickHouse{% sup %}®{% /sup %} and Tinybird

Building production-ready analytics APIs on top of ClickHouse{% sup %}®{% /sup %} typically requires significant infrastructure work—managing clusters, optimizing queries, handling scaling, and building secure API endpoints. Tinybird provides a [managed ClickHouse{% sup %}®{% /sup %} platform](https://www.tinybird.co/clickhouse) with features that support building analytics APIs, reducing the operational complexity typically involved in deploying production-ready analytics solutions.

[You can sign up to Tinybird for free](https://cloud.tinybird.co/signup) to build and test your first API in a few minutes.

## Additional resources

- [Date and Time Functions](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions)
- [Type Conversion Functions](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions)
- [Materialized Views](https://clickhouse.com/docs/en/engines/table-engines/special/materializedview)
- [MergeTree Engine](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree)
- [Roll up data with materialized views](https://www.tinybird.co/blog-posts/roll-up-data-with-materialized-views)
- [Real-time analytics with billion rows at scale](https://www.tinybird.co/blog-posts/real-time-analytics-with-billion-rows-at-scale)
- [Tinybird vs. ClickHouse{% sup %}®{% /sup %}: What's the difference?](https://www.tinybird.co/blog-posts/tinybird-vs-clickhouse)
- [Database Timestamps and Timezones Best Practices](https://www.tinybird.co/blog-posts/database-timestamps-timezones)

## FAQs about ISO week numbers in ClickHouse{% sup %}®{% /sup %}

### Does toISOWeek work in versions earlier than 19.15?

The `toISOWeek()` function was introduced in ClickHouse{% sup %}®{% /sup %} version 19.15. For older versions, use `toWeek(date, 3)` which provides identical ISO week calculation:

```sql
-- For ClickHouse® versions before 19.15
SELECT toWeek('2024-03-15', 3) AS iso_week_number;
```

### How do I extract both ISO week and ISO year together?

Combine `toISOWeek()` with `toISOYear()` to get proper year-week pairs, especially important around year boundaries:

```sql
SELECT
    date_column,
    toISOYear(date_column) AS iso_year,
    toISOWeek(date_column) AS iso_week,
    concat(toString(toISOYear(date_column)), '-W', toString(toISOWeek(date_column))) AS iso_week_string
FROM your_table;
```

### Is there a function that returns ISO weekday numbers?

Use `toDayOfWeek()` which returns 1-7 for Monday through Sunday, matching the ISO weekday numbering system:

```sql
SELECT
    date_column,
    toDayOfWeek(date_column) AS iso_weekday -- 1=Monday, 7=Sunday
FROM your_table;
```