How to extract ISO week numbers from dates in ClickHouse

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 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

The ISO week number in ClickHouse 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 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 version 19.15, so you won't find it in older installations.

The basic syntax looks like this:

SELECT toISOWeek(date_column) FROM table_name;

Sample query

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

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

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

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.

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
);

Input data types supported by toISOWeek

The toISOWeek() function works with various date and time data types in ClickHouse. Understanding which types work directly and which need conversion saves you debugging time later.

Date and Date32

Standard Date columns work directly with toISOWeek() without any conversion:

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

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:

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 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:

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.

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

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:

-- 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:

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:

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:

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:

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:

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:

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:

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

The MATERIALIZED keyword ensures that ClickHouse 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:

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:

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-based analytics APIs with ISO week numbers using Tinybird

A common business analytics pattern built with ClickHouse 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-based sales analytics API using Tinybird's managed ClickHouse platform.

Step 1: Create a data source for sales transactions

First, create a data source to store your sales data with proper ISO week calculation. Create a sales_transactions.datasource file:

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:

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:

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:

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:

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:

# 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:

tb --cloud deploy

Your APIs are now available in production:

# 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:

{
    "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 to create your own time series analytics APIs.

What to do next with ClickHouse and Tinybird

Building production-ready analytics APIs on top of ClickHouse typically requires significant infrastructure work—managing clusters, optimizing queries, handling scaling, and building secure API endpoints. Tinybird provides a managed ClickHouse platform 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 to build and test your first API in a few minutes.

Additional resources

• Date and Time Functions
• Type Conversion Functions
• Materialized Views
• MergeTree Engine
• Roll up data with materialized views
• Real-time analytics with billion rows at scale
• Tinybird vs. ClickHouse: What's the difference?
• Database Timestamps and Timezones Best Practices

FAQs about ISO week numbers in ClickHouse

Does toISOWeek work in versions earlier than 19.15?

The toISOWeek() function was introduced in ClickHouse version 19.15. For older versions, use toWeek(date, 3) which provides identical ISO week calculation:

-- 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:

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:

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