---
title: "How to extract ISO year in ClickHouse®: toISOYear vs toYear"
excerpt: "Learn how to extract ISO year numbers from dates in ClickHouse® using toISOYear(), including syntax examples, timezone handling, and performance tips."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-30 17:26:02"
publishedOn: "2025-09-30 17:26:02"
updatedOn: "2025-09-30 17:26:02"
status: "published"
---

Not all years are created equal. In ClickHouse{% sup %}®{% /sup %}, `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.

## Function signature and supported data types

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

```sql
toISOYear(date_expression)
```

The function works with ClickHouse{% sup %}®{% /sup %}'s primary temporal data types and handles timezone conversions automatically when working with datetime values.

### Accepted date and datetime variants

ClickHouse{% sup %}®{% /sup %}'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{% sup %}®{% /sup %} 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{% sup %}®{% /sup %}'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:

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

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

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

```sql
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{% sup %}®{% /sup %} applies timezone conversions before calculating ISO years, which means the same UTC timestamp can produce different results depending on your timezone setting:

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

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

```sql
-- 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{% sup %}®{% /sup %} projections work well for pre-computing ISO year values alongside your main data:

```sql
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](https://tinybird.co/docs/forward/work-with-data/optimize/materialized-views) provide excellent performance:

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

```sql
-- 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{% sup %}®{% /sup %} platform](https://www.tinybird.co/clickhouse) that we can use to build ClickHouse{% sup %}®{% /sup %}-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{% sup %}®{% /sup %} 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:

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

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

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

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

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

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

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

### Step 3: Query the weekly sales analytics APIs

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

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

```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": "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](https://cloud.tinybird.co/signup) to create your own weekly analytics APIs.

## Additional resources

- [Date and Time Functions](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions)
- [ClickHouse{% sup %}®{% /sup %} Aggregate Functions](https://clickhouse.com/docs/en/sql-reference/aggregate-functions)
- [Materialized Views](https://clickhouse.com/docs/en/engines/table-engines/special/materializedview)
- [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)

## Frequently asked questions about ISO year extraction

### How do I backfill ISO year for existing tables?

Use ClickHouse{% sup %}®{% /sup %}'s `ALTER TABLE UPDATE` statement to add ISO year values to existing records:

```sql
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{% sup %}®{% /sup %} version first supported `toISOYear()`?

The `toISOYear()` function has been available since early ClickHouse{% sup %}®{% /sup %} versions and works in all modern deployments, including ClickHouse{% sup %}®{% /sup %} 20.x and later. Tinybird's managed ClickHouse{% sup %}®{% /sup %} 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:

```tinybird
# 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.

## 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{% sup %}®{% /sup %} 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.