Name: Tinybird
Brand: Tinybird
Rating: 5.0 (10 reviews)

Not all years are created equal. In ClickHouse, toYear() extracts the familiar calendar year, but toISOYear() returns something different: the ISO 8601 week-numbering year that can start as early as December 29th or as late as January 4th.

This distinction matters more than you might expect, especially when building weekly reports or time series analytics where partial weeks at year boundaries can skew your data. We'll cover the function syntax, edge cases around timezone handling, performance optimization strategies, and how to build production APIs that leverage ISO year calculations effectively.

What toISOYear does and why ISO years matter

The toISOYear() function extracts the ISO year number from date or datetime values, returning a UInt16 integer that represents the year according to the ISO 8601 week-numbering system. Unlike calendar years that always start on January 1st, ISO years begin on the Monday of the first week that contains at least four days of the new year.

This distinction matters because ISO years can start as early as December 29th or as late as January 4th of the calendar year. Consider December 31, 2023: while it belongs to calendar year 2023, it falls into ISO year 2024 because it's part of the first ISO week of 2024.

For weekly BI reports and time series analytics, ISO years provide consistent week-based groupings that align with how many organizations actually operate. You get complete weeks rather than arbitrary calendar boundaries that can split weeks across different years.

Start building with Tinybird!

If you've read this far, you might want to use Tinybird as your analytics backend. You can just get started, on the free plan.

Function signature and supported data types

The toISOYear() function accepts a single argument containing date or datetime information and follows this basic syntax:

toISOYear(date_expression)

The function works with ClickHouse's primary temporal data types and handles timezone conversions automatically when working with datetime values.

Accepted date and datetime variants

ClickHouse's toISOYear() function accepts three main temporal data types:

Date: Standard date values like '2024-01-01'
DateTime: Date and time combinations such as '2024-01-01 15:30:00'
DateTime64: High-precision datetime values with subsecond accuracy

When you pass DateTime or DateTime64 values, ClickHouse applies the server's timezone setting or the column's specified timezone before calculating the ISO year. The same UTC timestamp might produce different ISO years depending on the timezone context.

Return type and nullability

The function returns a UInt16 integer representing the ISO year, which can handle years from 1900 to 2155, more than sufficient for typical analytical workloads. If you pass a NULL value as input, toISOYear() returns NULL, maintaining ClickHouse's standard NULL propagation behavior.

For invalid date expressions, the function throws an exception rather than returning NULL. You'll want to validate your input data or use functions like parseDateTimeBestEffortOrNull() for uncertain date formats.

Basic query examples with date and DateTime64

Let's start with straightforward examples that demonstrate toISOYear() in action. The patterns work whether you're querying literal values or actual table columns.

Example with literal date

Here's how toISOYear() handles different date scenarios:

SELECT 
    toISOYear('2024-01-01') AS new_year_iso,
    toISOYear('2023-12-31') AS dec_31_iso,
    toISOYear('2024-12-30') AS dec_30_iso;

This query returns 2024, 2024, and 2025 respectively. Notice how December 31, 2023 belongs to ISO year 2024, while December 30, 2024 already belongs to ISO year 2025. The edge cases highlight exactly why ISO years matter for week-based analytics.

Example with ingested DateTime64 column

When working with actual table data, you'll typically apply toISOYear() in GROUP BY clauses for time-based aggregations:

SELECT 
    toISOYear(event_timestamp) AS iso_year,
    COUNT(*) AS event_count,
    AVG(revenue) AS avg_revenue
FROM sales_events 
WHERE event_timestamp >= '2023-01-01'
GROUP BY toISOYear(event_timestamp)
ORDER BY iso_year;

This pattern works particularly well for weekly reporting where you want complete weeks rather than partial weeks at year boundaries. You can combine it with toISOWeek() for even more granular time-based groupings.

toISOYear vs toYear vs toISOWeek

The differences between the three functions become apparent at year boundaries. While they might seem similar, they serve distinct analytical purposes.

Calendar year differences

The key difference between toISOYear() and toYear() becomes apparent at year boundaries:

Date	toYear()	toISOYear()	Explanation
2023-12-31	2023	2024	Last day of 2023 belongs to ISO year 2024
2024-01-01	2024	2024	First day aligns with ISO year
2025-01-05	2025	2025	Standard alignment case
2024-12-30	2024	2025	Late December belongs to next ISO year

The differences compound when you're analyzing weekly patterns or building dashboards that show complete weeks rather than partial ones.

Group-by behaviour

When used in GROUP BY clauses, each function creates different groupings:

-- Calendar year grouping
SELECT toYear(order_date) AS year, COUNT(*) 
FROM orders GROUP BY toYear(order_date);

-- ISO year grouping  
SELECT toISOYear(order_date) AS iso_year, COUNT(*) 
FROM orders GROUP BY toISOYear(order_date);

The ISO year grouping ensures that all weeks are complete, while calendar year grouping might split the first and last weeks across different groups.

When to combine functions

You'll often combine toISOYear() with toISOWeek() for comprehensive time-based analysis:

SELECT 
    toISOYear(event_date) AS iso_year,
    toISOWeek(event_date) AS iso_week,
    COUNT(*) AS weekly_events
FROM user_events 
GROUP BY toISOYear(event_date), toISOWeek(event_date)
ORDER BY iso_year, iso_week;

This combination gives you both the year context and week number, creating a complete ISO week identifier that's perfect for time series analysis.

Handling time zones and week 53 edge cases

Working with ISO years involves several edge cases that can trip up developers, especially around timezone handling and the occasional 53-week year.

Server time zone setting

ClickHouse applies timezone conversions before calculating ISO years, which means the same UTC timestamp can produce different results depending on your timezone setting:

-- Assuming server timezone is UTC
SELECT toISOYear('2024-01-01 02:00:00') AS utc_iso_year;

-- Converting to different timezone first
SELECT toISOYear(toTimeZone('2024-01-01 02:00:00', 'America/New_York')) AS ny_iso_year;

If your data spans multiple timezones, consider standardizing to UTC before applying toISOYear(). You can also explicitly convert to your business timezone using toTimeZone().

Last days of December

December dates require special attention because they frequently belong to the following ISO year:

SELECT 
    date_val,
    toYear(date_val) AS calendar_year,
    toISOYear(date_val) AS iso_year,
    CASE 
        WHEN toYear(date_val) != toISOYear(date_val) 
        THEN 'Year mismatch!' 
        ELSE 'Aligned' 
    END AS status
FROM (
    SELECT arrayJoin([
        '2023-12-29', '2023-12-30', '2023-12-31',
        '2024-01-01', '2024-01-02'
    ]) AS date_val
);

This query helps identify dates where calendar and ISO years diverge, useful for validating your time-based groupings.

First days of January

Similarly, early January dates sometimes belong to the previous ISO year. You can check for this pattern:

-- Check if January dates belong to previous ISO year
SELECT 
    date_val,
    toISOYear(date_val) AS iso_year,
    toISOYear(date_val) < toYear(date_val) AS belongs_to_prev_year
FROM generate_series('2020-01-01'::Date, '2025-01-07'::Date, INTERVAL 1 DAY)
WHERE toISOYear(date_val) != toYear(date_val);

Recognizing the patterns helps you build more robust date-filtering logic in your applications.

Performance tips and when to materialize ISO year

For production workloads with large datasets, optimizing ISO year calculations can significantly impact query performance. Here are proven strategies for handling high-volume scenarios.

Projection and index strategy

ClickHouse projections work well for pre-computing ISO year values alongside your main data:

ALTER TABLE events 
ADD PROJECTION iso_year_projection (
    SELECT 
        toISOYear(event_date) AS iso_year,
        event_type,
        COUNT(),
        SUM(revenue)
    GROUP BY iso_year, event_type
);

This projection accelerates queries that group by ISO year, especially when combined with other dimensions like event type or user segment.

Creating a materialized column

For frequently-accessed ISO year values, materialized columns provide excellent performance:

ALTER TABLE user_events 
ADD COLUMN event_iso_year UInt16 MATERIALIZED toISOYear(event_timestamp);

-- Create an index on the materialized column
ALTER TABLE user_events 
ADD INDEX iso_year_idx event_iso_year TYPE set(100) GRANULARITY 1;

The materialized column gets computed once during insertion and stored alongside your data. This eliminates repeated function calls during queries.

Impact on aggregations

Pre-computed ISO years dramatically improve GROUP BY performance:

-- Slower: computing ISO year during query
SELECT toISOYear(event_date) AS iso_year, COUNT(*) 
FROM events 
GROUP BY toISOYear(event_date);

-- Faster: using materialized column
SELECT event_iso_year, COUNT(*) 
FROM events 
GROUP BY event_iso_year;

The performance difference becomes more pronounced as your dataset grows. We often see 2-3x improvements for large aggregations.

Building an analytics API that accounts for ISO year boundaries

A common analytics pattern involves aggregating data by week for the current calendar year while ensuring complete weeks using ISO year standards. This approach prevents partial weeks from skewing your metrics and aligns with business reporting conventions.

Tinybird is a managed ClickHouse platform that we can use to build ClickHouse-based real-time analytics APIs that our application can fetch.

Here's how to build an example weekly sales analytics dashboard using Tinybird's managed ClickHouse platform and the toISOYear() function.

Step 1: Create a data source for sales events

First, create a data source to store sales transactions with proper timestamp handling. Create a sales_transactions.datasource file:

SCHEMA >
    `transaction_id` String,
    `timestamp` DateTime64(3, 'UTC'),
    `customer_id` String,
    `product_id` String,
    `product_category` LowCardinality(String),
    `sales_amount` Float64,
    `quantity` UInt32,
    `sales_rep_id` String,
    `region` LowCardinality(String),
    `channel` LowCardinality(String)

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, customer_id"

Deploy the data source:

tb --cloud deploy

Ingest sample sales data using the Events API:

curl -X POST \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  -H "Content-Type: application/json" \
  "https://api.tinybird.co/v0/events?name=sales_transactions" \
  -d '[
    {"transaction_id": "txn_001", "timestamp": "2024-01-08 10:30:00", "customer_id": "cust_123", "product_id": "prod_456", "product_category": "Electronics", "sales_amount": 299.99, "quantity": 1, "sales_rep_id": "rep_001", "region": "North", "channel": "online"},
    {"transaction_id": "txn_002", "timestamp": "2024-01-15 14:22:00", "customer_id": "cust_124", "product_id": "prod_457", "product_category": "Clothing", "sales_amount": 89.50, "quantity": 2, "sales_rep_id": "rep_002", "region": "South", "channel": "retail"},
    {"transaction_id": "txn_003", "timestamp": "2024-02-12 09:15:00", "customer_id": "cust_125", "product_id": "prod_458", "product_category": "Electronics", "sales_amount": 1299.99, "quantity": 1, "sales_rep_id": "rep_001", "region": "East", "channel": "online"},
    {"transaction_id": "txn_004", "timestamp": "2024-12-30 16:45:00", "customer_id": "cust_126", "product_id": "prod_459", "product_category": "Home", "sales_amount": 199.99, "quantity": 3, "sales_rep_id": "rep_003", "region": "West", "channel": "retail"}
  ]'

Step 2: Create weekly sales analytics API with ISO year boundaries

Create a pipe that aggregates sales by complete ISO weeks for the current calendar year. Create weekly_sales_analytics.pipe:

NODE weekly_sales_analysis
SQL >
    %
    WITH current_year AS (
        SELECT toYear(now()) AS year
    ),
    iso_year_bounds AS (
        SELECT 
            year,
            -- Get first Monday of first complete ISO week in calendar year
            toMonday(toDate(year || '-01-04')) AS first_iso_week_start,
            -- Get last Sunday of last complete ISO week in calendar year  
            toSunday(toDate(year || '-12-28')) AS last_iso_week_end
        FROM current_year
    )
    SELECT 
        toISOYear(timestamp) AS iso_year,
        toISOWeek(timestamp) AS iso_week,
        -- Week boundaries for reference
        toMonday(timestamp) AS week_start,
        toSunday(timestamp) AS week_end,
        product_category,
        region,
        channel,
        -- 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,
        -- Week-over-week calculations
        round(sum(sales_amount) / 7, 2) AS daily_avg_sales
    FROM sales_transactions
    CROSS JOIN iso_year_bounds
    WHERE timestamp >= iso_year_bounds.first_iso_week_start
      AND timestamp <= iso_year_bounds.last_iso_week_end + INTERVAL 1 DAY
      -- Calendar year filter (ensures we stay within requested year)
      {\% if defined(calendar_year) %}
      AND toYear(timestamp) = {{UInt16(calendar_year, toYear(now()))}}
      {\% else %}
      AND toYear(timestamp) = (SELECT year FROM current_year)
      {\% end %}
      -- Optional filters
      {\% if defined(category_filter) %}
      AND product_category = {{String(category_filter)}}
      {\% end %}
      {\% if defined(region_filter) %}
      AND region = {{String(region_filter)}}
      {\% end %}
      {\% if defined(channel_filter) %}
      AND channel = {{String(channel_filter)}}
      {\% end %}
      {\% if defined(min_week) %}
      AND toISOWeek(timestamp) >= {{UInt8(min_week, 1)}}
      {\% end %}
      {\% if defined(max_week) %}
      AND toISOWeek(timestamp) <= {{UInt8(max_week, 53)}}
      {\% end %}
    GROUP BY 
        iso_year, 
        iso_week, 
        week_start, 
        week_end,
        product_category, 
        region, 
        channel
    ORDER BY iso_year, iso_week, total_sales DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 100)}}
    {\% end %}

TYPE ENDPOINT

Create a summary API for year-to-date performance. Create ytd_sales_summary.pipe:

NODE ytd_summary
SQL >
    %
    WITH current_year AS (
        SELECT toYear(now()) AS year
    ),
    complete_weeks_only AS (
        SELECT 
            toISOYear(timestamp) AS iso_year,
            toISOWeek(timestamp) AS iso_week,
            sum(sales_amount) AS weekly_sales,
            count() AS weekly_transactions,
            uniq(customer_id) AS weekly_customers
        FROM sales_transactions
        CROSS JOIN current_year
        WHERE toYear(timestamp) = current_year.year
          -- Only include complete weeks (Monday to Sunday)
          AND toISOYear(timestamp) = toYear(timestamp)
        GROUP BY iso_year, iso_week
    )
    SELECT 
        iso_year,
        count() AS completed_weeks,
        sum(weekly_sales) AS ytd_total_sales,
        avg(weekly_sales) AS avg_weekly_sales,
        sum(weekly_transactions) AS ytd_transactions,
        sum(weekly_customers) AS ytd_unique_customers,
        -- Performance metrics
        max(weekly_sales) AS best_week_sales,
        min(weekly_sales) AS worst_week_sales,
        round((max(weekly_sales) - min(weekly_sales)) / avg(weekly_sales) * 100, 2) AS sales_volatility_percent,
        -- Trend indicators (last 4 weeks vs previous 4 weeks)
        (SELECT avg(weekly_sales) FROM complete_weeks_only cw2 WHERE cw2.iso_week > (max(complete_weeks_only.iso_week) - 4)) AS recent_4week_avg,
        (SELECT avg(weekly_sales) FROM complete_weeks_only cw3 WHERE cw3.iso_week BETWEEN (max(complete_weeks_only.iso_week) - 8) AND (max(complete_weeks_only.iso_week) - 4)) AS previous_4week_avg
    FROM complete_weeks_only
    GROUP BY iso_year
    ORDER BY iso_year

TYPE ENDPOINT

Deploy the API endpoints:

tb --cloud deploy

Step 3: Query the weekly sales analytics APIs

Your ISO year-aware weekly sales APIs are now available:

# Get weekly sales breakdown for current year
curl "https://api.tinybird.co/v0/pipes/weekly_sales_analytics.json" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Filter by specific calendar year and product category
curl "https://api.tinybird.co/v0/pipes/weekly_sales_analytics.json?calendar_year=2024&category_filter=Electronics" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Analyze Q1 performance (weeks 1-13)
curl "https://api.tinybird.co/v0/pipes/weekly_sales_analytics.json?min_week=1&max_week=13&region_filter=North" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Get year-to-date summary with performance metrics
curl "https://api.tinybird.co/v0/pipes/ytd_sales_summary.json" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Compare online vs retail channel performance
curl "https://api.tinybird.co/v0/pipes/weekly_sales_analytics.json?channel_filter=online&limit=20" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

The APIs return comprehensive weekly analytics with ISO year alignment:

{
    "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": "channel", "type": "String"},
        {"name": "transaction_count", "type": "UInt64"},
        {"name": "total_sales", "type": "Float64"},
        {"name": "avg_transaction_value", "type": "Float64"},
        {"name": "total_quantity", "type": "UInt64"},
        {"name": "unique_customers", "type": "UInt64"},
        {"name": "active_sales_reps", "type": "UInt64"},
        {"name": "daily_avg_sales", "type": "Float64"}
    ],
    "data": [
        {
            "iso_year": 2024,
            "iso_week": 2,
            "week_start": "2024-01-08",
            "week_end": "2024-01-14",
            "product_category": "Electronics",
            "region": "North",
            "channel": "online",
            "transaction_count": 1,
            "total_sales": 299.99,
            "avg_transaction_value": 299.99,
            "total_quantity": 1,
            "unique_customers": 1,
            "active_sales_reps": 1,
            "daily_avg_sales": 42.86
        }
    ],
    "rows": 1,
    "statistics": {
        "elapsed": 0.004,
        "rows_read": 4,
        "bytes_read": 512
    }
}

This approach provides:

Complete week boundaries using ISO year standards to prevent partial week distortions
Calendar year alignment ensuring analysis stays within business reporting periods
Multi-dimensional analytics with product, region, and channel breakdowns
Performance metrics including volatility and trend indicators
Flexible filtering by time ranges, categories, and business dimensions
Year-to-date summaries with comparative performance analysis

Your sales data becomes available as production-ready APIs that automatically handle ISO year complexities while providing business-relevant weekly insights. Start building with Tinybird's free plan to create your own weekly analytics APIs.

Additional resources

Frequently asked questions about ISO year extraction

How do I backfill ISO year for existing tables?

Use ClickHouse's ALTER TABLE UPDATE statement to add ISO year values to existing records:

ALTER TABLE events 
UPDATE iso_year = toISOYear(event_date)
WHERE iso_year = 0;

For large tables, consider processing data in batches using date range filters to avoid overwhelming your cluster. You might also add the materialized column first, then backfill historical data separately.

Which ClickHouse version first supported `toISOYear()`?

The toISOYear() function has been available since early ClickHouse versions and works in all modern deployments, including ClickHouse 20.x and later. Tinybird's managed ClickHouse platform includes full support for all ISO date functions out of the box.

Can I calculate ISO year inside MaterializeMySQL or Kafka connector streams?

Yes, you can include toISOYear() calculations in computed columns when defining your data source:

# kafka_events.datasource
SCHEMA >
    raw_timestamp DateTime64(3),
    event_data String,
    iso_year UInt16 `toISOYear(raw_timestamp)`

ENGINE MergeTree
ENGINE_SORTING_KEY raw_timestamp

This approach computes ISO years during ingestion, eliminating the need for runtime calculations in your queries.

Subscribe to SCHEMA > Evolution

We are Tinybird and we manage data for companies like Vercel and Canva. Plus, write a newsletter covering Data, AI and everything that matters in between. Join us.

Key takeaways and next steps with Tinybird

The toISOYear() function provides essential functionality for week-based analytics, ensuring your time series groupings align with business reporting needs rather than arbitrary calendar boundaries. Whether you're building weekly dashboards, fiscal year reports, or time-based APIs, the distinction between ISO years and calendar years helps you create more accurate analytics.

For production deployments, consider materializing ISO year values for frequently-queried datasets and use projections to accelerate common aggregation patterns. The performance benefits compound as your data volume grows, making optimizations crucial for scalable analytics.

Tinybird's managed ClickHouse platform significantly reduces infrastructure complexity for scalable analytics. With built-in API generation, authentication, and monitoring, you can deploy ISO year analytics that scale from prototype to production while minimizing operational overhead.

For more information on building real-time analytics with ISO year support, refer to Tinybird's documentation and resources.

Skip the infra work. Deploy your first ClickHouse project now

Product /

Company /

Resources /

Integrations /

Use Cases /

How to extract ISO year in ClickHouse: toISOYear vs toYear