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.
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®ion_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
- Date and Time Functions
- ClickHouse Aggregate Functions
- Materialized Views
- Real-time analytics with billion rows at scale
- Tinybird vs. ClickHouse: What's the difference?
- Database Timestamps and Timezones Best Practices
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.
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.