How to create a date from year, month, and day components in ClickHouse^®

Dates are often stored or received as separate values for year, month, and day. In ClickHouse^®, it is common to transform these separate values into a single Date type for querying, filtering, or storing data.

The ClickHouse^® SQL language includes several functions for working with dates and times. One of the most direct ways to construct a Date value from its components is with the makeDate function.

This guide explains, step by step, how to create a Date from year, month, and day values in ClickHouse^®. The examples use the current date, September 18, 2025, to show how the function works.

The fastest way to build a date from components

The simplest way to create a Date from year, month, and day in ClickHouse^® is to use the makeDate(year, month, day) function. This function takes three integer arguments and returns a Date value.

Here's the basic syntax:

SELECT makeDate(2025, 9, 18) AS event_date;

The output shows the constructed date:

┌─event_date─┐
│ 2025-09-18 │
└────────────┘

Running makeDate in a SELECT statement

You can test the makeDate function directly in a SELECT statement without any table. This approach lets you see exactly how the function works:

SELECT
    makeDate(2025, 9, 18) AS today,
    makeDate(2024, 2, 29) AS leap_day,
    makeDate(2023, 12, 31) AS year_end;

The function accepts integers for each component and returns properly formatted Date values.

Inserting dates using makeDate in VALUES

When inserting data into a table, makeDate works inside INSERT statements. If you have a table called events with a Date column named event_date:

INSERT INTO events (event_date, description)
VALUES (makeDate(2025, 9, 18), 'Product launch');

This approach is useful when your source data provides year, month, and day as separate fields rather than as a formatted date string.

makeDate function syntax and version support

The makeDate function comes in two forms. The standard version takes three arguments:

makeDate(year, month, day)

A second version accepts just two arguments:

makeDate(year, day_of_year)

In the two-argument form, day_of_year represents the day number within the year (1 to 365, or 366 for leap years).

• Function availability: makeDate was introduced in ClickHouse^® version 22.6
• Return type: The function returns a Date value
• Supported range: Date values work from 1970-01-01 to 2149-06-06

Each argument accepts integer, float, or decimal input types. ClickHouse^® converts these to integers internally before constructing the date.

Parameter rules and validation logic

The makeDate function validates all inputs to confirm they form a real calendar date. If any value falls outside acceptable ranges, ClickHouse^® returns an error rather than creating an invalid date.

Month and day ranges

Month values work from 1 to 12, where 1 represents January and 12 represents December. Day values depend on the specific month and year:

• January, March, May, July, August, October, December: 1 to 31 days
• April, June, September, November: 1 to 30 days
• February: 1 to 28 days (or 29 in leap years)

If you try makeDate(2025, 13, 1), ClickHouse^® will throw an error because month 13 doesn't exist.

Leap year handling

February 29 only works in leap years. ClickHouse^® follows standard leap year rules:

• Years divisible by 4 are leap years
• Years divisible by 100 are not leap years
• Years divisible by 400 are leap years

So makeDate(2024, 2, 29) works because 2024 is a leap year, but makeDate(2025, 2, 29) fails because 2025 is not.

Choosing between makeDate, makeDate32, makeDateTime, and YYYYMMDDToDate

ClickHouse^® provides several functions for creating dates from numeric inputs. Each serves different use cases:

Date range limits

The standard Date type in ClickHouse^® covers dates from 1970-01-01 through 2149-06-06. For dates outside this range, use makeDate32, which returns a Date32 value with a much broader range.

-- This works with makeDate
SELECT makeDate(2100, 1, 1) AS future_date;

-- For dates beyond 2149, use makeDate32
SELECT makeDate32(2200, 1, 1) AS far_future_date;

Time zone requirements

The makeDate and makeDate32 functions create date-only values without time or timezone information. For datetime values that include time components and timezone handling, use makeDateTime:

SELECT makeDateTime(2025, 9, 18, 14, 30, 0, 'America/New_York') AS datetime_with_tz;

Converting string or integer formats to date

Many datasets store dates as strings or integers rather than separate components. ClickHouse^® provides multiple conversion options for these formats.

ClickHouse^® string to datetime patterns

For converting strings to dates, ClickHouse^® offers several parsing functions:

• toDate(): Converts ISO format strings like '2025-09-18'
• parseDateTime(): Handles custom format patterns
• parseDateTimeBestEffort(): Attempts to parse various string formats automatically

-- Simple string conversion
SELECT toDate('2025-09-18') AS simple_conversion;

-- Custom format parsing
SELECT parseDateTime('18/09/2025 14:30:00', '%d/%m/%Y %H:%i:%s') AS custom_format;

-- Best effort parsing
SELECT parseDateTimeBestEffort('September 18, 2025') AS flexible_parsing;

The toDateTime function works similarly for strings that include time components.

YYYYMMDDToDate usage

When dates are stored as 8-digit integers (like 20250918 for September 18, 2025), use YYYYMMDDToDate:

SELECT YYYYMMDDToDate(20250918) AS converted_date;

This function is particularly useful when working with data exports from systems that format dates as integers. For extended date ranges, YYYYMMDDToDate32 provides the same functionality with Date32 output.

Bulk column conversion in existing tables

Real-world scenarios often involve tables with separate year, month, and day columns that need combining into a single Date column. ClickHouse^® handles this through table updates or materialized views.

ALTER TABLE UPDATE pattern

When a table already contains separate date component columns, use ALTER TABLE ... UPDATE to populate a new Date column:

-- Add a new Date column to the existing table
ALTER TABLE events ADD COLUMN event_date Date;

-- Populate the new column using existing year, month, day columns
ALTER TABLE events
UPDATE event_date = makeDate(year, month, day)
WHERE event_date IS NULL;

This approach works well for one-time conversions of existing data.

Creating a materialized view

For ongoing data ingestion where new rows arrive with separate date components, a materialized view automatically handles the conversion. For a comprehensive guide on what materialized views are and why they matter for real-time analytics, this approach can significantly improve performance for date construction workflows:

CREATE MATERIALIZED VIEW events_with_dates
ENGINE = MergeTree()
ORDER BY event_date
AS
SELECT
    makeDate(year, month, day) AS event_date,
    event_type,
    value
FROM raw_events;

This view processes each new row from raw_events, constructs the date, and stores it alongside other columns.

Building a time series analytics API with Tinybird

Tinybird excels at time series analytics by combining ClickHouse^®'s columnar performance and aggregation capabilities with real-time API generation and hosting. Here's how to build an analytics API endpoint that constructs dates from components and aggregates user engagement metrics over time.

Create the time series data source

Start with event analytics data containing separate date components. For example, you could create an NDJSON file named user_events.ndjson:

{"user_id": "u1", "event_type": "page_view", "year": 2025, "month": 9, "day": 15, "hour": 14, "duration_seconds": 45}
{"user_id": "u2", "event_type": "conversion", "year": 2025, "month": 9, "day": 15, "hour": 14, "duration_seconds": 120}
{"user_id": "u1", "event_type": "page_view", "year": 2025, "month": 9, "day": 16, "hour": 9, "duration_seconds": 30}
{"user_id": "u3", "event_type": "page_view", "year": 2025, "month": 9, "day": 16, "hour": 10, "duration_seconds": 75}
{"user_id": "u2", "event_type": "conversion", "year": 2025, "month": 9, "day": 16, "hour": 11, "duration_seconds": 90}

Upload this as a Tinybird data source:

tb datasource create --file user_events.ndjson --name user_events
tb --cloud deploy
tb datasource append user_events user_events.ndjson

Build the aggregation pipe

Create a pipe that constructs dates and aggregates metrics by time periods:

NODE daily_metrics
SQL >
    %
    WITH constructed_dates AS (
        SELECT
            user_id,
            event_type,
            makeDate(year, month, day) AS event_date,
            makeDateTime(year, month, day, hour, 0, 0) AS event_datetime,
            duration_seconds
        FROM user_events
        WHERE makeDate(year, month, day) BETWEEN
            {{Date(start_date, '2025-09-01')}} AND
            {{Date(end_date, '2025-09-30')}}
    )
    SELECT
        event_date,
        COUNT(*) AS total_events,
        uniq(user_id) AS unique_users,
        countIf(event_type = 'conversion') AS conversions,
        countIf(event_type = 'page_view') AS page_views,
        round(countIf(event_type = 'conversion') / uniq(user_id) * 100, 2) AS conversion_rate_percent,
        round(avg(duration_seconds), 1) AS avg_duration_seconds,
        min(event_datetime) AS first_event_time,
        max(event_datetime) AS last_event_time
    FROM constructed_dates
    GROUP BY event_date
    ORDER BY event_date
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 30)}}
    {\% end %}

TYPE ENDPOINT

This query demonstrates ClickHouse^®'s time series strengths:

• Date construction from separate year/month/day fields using makeDate()
• Time aggregation with GROUP BY on constructed dates
• Unique counting with uniq() for distinct users
• Conditional aggregation with countIf() for event types
• Statistical functions like avg(), min(), max()
• Dynamic date ranges with parameterized start/end dates

For more advanced time series generation techniques, ClickHouse^® provides powerful functions for creating complete time series data from scratch.

Deploy and query the analytics API

Deploy the pipe:

tb --cloud deploy

Query daily metrics for a date range:

curl "https://api.tinybird.co/v0/pipes/daily_metrics.json?start_date=2025-09-15&end_date=2025-09-16&limit=10"

The response provides aggregated time series analytics:

{
  "data": [
    {
      "event_date": "2025-09-15",
      "total_events": 2,
      "unique_users": 2,
      "conversions": 1,
      "page_views": 1,
      "conversion_rate_percent": 50.0,
      "avg_duration_seconds": 82.5,
      "first_event_time": "2025-09-15 14:00:00",
      "last_event_time": "2025-09-15 14:00:00"
    },
    {
      "event_date": "2025-09-16",
      "total_events": 3,
      "unique_users": 3,
      "conversions": 1,
      "page_views": 2,
      "conversion_rate_percent": 33.33,
      "avg_duration_seconds": 65.0,
      "first_event_time": "2025-09-16 09:00:00",
      "last_event_time": "2025-09-16 11:00:00"
    }
  ]
}

This approach showcases how ClickHouse^®'s date construction functions enable powerful time series analytics, transforming raw event components into actionable business metrics.

Next steps with Tinybird for hassle-free ClickHouse^®

Working with dates and times becomes much easier when you don't have to manage the underlying ClickHouse^® infrastructure yourself. Managed platforms like Tinybird handle the technical complexity while providing tools for data ingestion, schema evolution, and API deployment.

Tinybird supports SQL-based development with version control and observability, with zero need to handle infrastructure maintenance, scaling, or DevOps.

A free plan is available for exploring the platform and building proof-of-concept applications. Sign up for free

FAQs about ClickHouse^® date construction

Can makeDate accept string arguments instead of integers?

ClickHouse^® automatically converts string representations of numbers to integers for the makeDate function. Using makeDate('2025', '9', '18') works the same as makeDate(2025, 9, 18).

What happens when makeDate receives invalid date components?

ClickHouse^® throws an error if the provided values don't form a valid calendar date. For example, makeDate(2025, 2, 30) fails because February never has 30 days.

Does makeDate behavior change across different time zones?

The makeDate function produces identical results regardless of server timezone settings. Date values in ClickHouse^® represent calendar days without any time or timezone context.

Can I use makeDate with NULL values in the arguments?

If any argument to makeDate is NULL, the function returns NULL rather than throwing an error. This behavior allows the function to work safely with datasets that contain missing date components.