How to extract year, month, day, and time components from dates in ClickHouse

When you're analyzing time-series data in ClickHouse, pulling apart timestamps into their individual components (year, quarter, month, day, week, hour, minute, second, or millisecond) happens constantly. Whether you're grouping sales by quarter, filtering events by business hours, or building time-based aggregations, these extractions form the backbone of temporal analytics.

This guide covers ClickHouse's built-in time extraction functions, data type considerations, timezone handling, and performance optimization techniques for high-volume scenarios. You'll also see how to build production-ready APIs that serve extracted time components through Tinybird's managed ClickHouse platform.

Time extraction functions in ClickHouse

ClickHouse gives you dedicated functions to pull specific time pieces from Date, DateTime, and DateTime64 values. When you run toYear() on a timestamp, you get back 2024. When you run toMonth(), you get 12. When you run toHour() on 2:00 PM, you get 14.

The extraction functions work across ClickHouse's different date and time data types, though some functions like toMillisecond() only work with specific precision levels. You'll use the extraction functions frequently when building time-based analytics, grouping data by time periods, or filtering by specific date components.

toYear, toQuarter, toMonth

The toYear() function extracts the four-digit year from any date or datetime value:

SELECT toYear(now()) AS current_year;
-- Returns: 2024

toQuarter() returns which quarter of the year you're in, returning 1 through 4:

SELECT toQuarter('2024-07-15') AS quarter;
-- Returns: 3

toMonth() extracts the month as a number from 1 to 12:

SELECT toMonth('2024-12-25') AS month;
-- Returns: 12

The quarter mapping works exactly like you'd expect: Q1 covers January through March, Q2 covers April through June, Q3 covers July through September, and Q4 covers October through December.

toDayOfMonth, toDayOfWeek, toDayOfYear

toDayOfMonth() returns the day within the month, so the 15th gives you 15:

SELECT toDayOfMonth('2024-03-15') AS day;
-- Returns: 15

toDayOfWeek() returns the day of the week where Monday equals 1 and Sunday equals 7:

SELECT toDayOfWeek('2024-03-15') AS weekday;
-- Returns: 5 (Friday)

toDayOfYear() calculates which day of the year you're on, from 1 to 366:

SELECT toDayOfYear('2024-12-31') AS day_of_year;
-- Returns: 366 (2024 is a leap year)

The week numbering follows the ISO 8601 standard, which means Monday starts the week. ClickHouse automatically handles leap years when calculating day-of-year values, so February 29th gets counted properly in leap years.

toWeek, toHour, toMinute, toSecond

toWeek() returns the ISO week number from 1 to 53 using ISO 8601 week numbering:

SELECT toWeek('2024-01-01') AS week_number;
-- Returns: 1

The time extraction functions work with DateTime and DateTime64 values:

SELECT 
    toHour('2024-03-15 14:30:45') AS hour,
    toMinute('2024-03-15 14:30:45') AS minute,
    toSecond('2024-03-15 14:30:45') AS second;
-- Returns: hour=14, minute=30, second=45

ISO week numbering defines week 1 as the week containing the first Thursday of the year. This sometimes means January 1st falls in week 52 or 53 of the previous year. When you apply time extraction functions to Date types without time components, they return 0 for hour, minute, and second values.

toMillisecond with DateTime64

toMillisecond() only works with DateTime64 data types that store sub-second precision. Regular DateTime values always return 0 because they only store down to the second:

-- This returns 0 (DateTime has no millisecond precision)
SELECT toMillisecond(now()) AS ms;

-- This works with DateTime64
SELECT toMillisecond(now64(3)) AS ms;
-- Returns: 0-999 (milliseconds)

You can extract milliseconds from existing DateTime64 columns in your tables:

CREATE TABLE events (
    timestamp DateTime64(3),
    event_name String
) ENGINE = MergeTree ORDER BY timestamp;

SELECT toMillisecond(timestamp) AS ms FROM events;

DateTime64 requires an explicit precision parameter when you create the column. The number 3 means milliseconds, 6 means microseconds, and 9 means nanoseconds.

Supported data types and precision

ClickHouse's date and time data types determine which extraction functions work and how precise your results will be. Date stores only the calendar date without time information, while DateTime includes both date and time with second precision.

Date vs DateTime

When you apply time extraction functions to Date columns, they return 0 for any time components:

SELECT 
    toHour('2024-03-15'::Date) AS hour,      -- Returns: 0
    toHour('2024-03-15 14:30:45') AS hour_dt -- Returns: 14

The Date type works fine for date-only analytics where you don't care about specific times during the day. DateTime gives you second-level precision, which covers most use cases for event tracking and time-series analysis.

DateTime64 for sub-second precision

DateTime64 extends DateTime with configurable sub-second precision up to nanoseconds. The precision parameter determines how many digits after the decimal point get stored:

-- DateTime64(3) stores milliseconds
SELECT now64(3) AS timestamp_ms;
-- Output: 2024-03-15 14:30:45.123

-- DateTime64(6) stores microseconds  
SELECT now64(6) AS timestamp_us;
-- Output: 2024-03-15 14:30:45.123456

Only DateTime64 columns give you meaningful results from toMillisecond(). Regular DateTime columns always return 0 because they don't store sub-second precision.

Higher precision levels use more storage space and can slow down queries when you're working with massive datasets. Most applications work fine with millisecond precision unless you're tracking high-frequency financial data or measuring system performance.

Handling time zones safely

Time zone settings affect the values you extract from datetime columns, especially for hour, day, and week calculations. ClickHouse applies the session timezone when extracting components unless you explicitly convert to a different timezone first.

System settings that affect extraction

The timezone setting determines how ClickHouse interprets DateTime values during component extraction:

-- Check current timezone
SELECT timezone() AS current_tz;

-- Same UTC timestamp, different timezone interpretations
SET timezone = 'UTC';
SELECT toHour('2024-03-15 14:30:45') AS utc_hour;
-- Returns: 14

SET timezone = 'America/New_York';  
SELECT toHour('2024-03-15 14:30:45') AS ny_hour;
-- Returns: 10 (UTC-4 during daylight saving)

This timezone interpretation can mess up day-boundary calculations and weekly aggregations. A timestamp at 2 AM UTC might fall on different calendar days depending on your timezone setting.

The timezone setting affects the entire session, so all your time extractions will use that timezone unless you override it explicitly. This can cause subtle bugs when you're aggregating data across different time zones or when daylight saving time transitions happen.

Converting between time zones

Use toTimeZone() to explicitly convert datetime values before extraction:

SELECT 
    toHour(toTimeZone('2024-03-15 02:30:45', 'UTC')) AS utc_hour,
    toHour(toTimeZone('2024-03-15 02:30:45', 'Asia/Tokyo')) AS tokyo_hour;
-- Returns: utc_hour=2, tokyo_hour=11

This approach gives you precise control over which timezone's calendar the extraction uses:

-- Group events by Tokyo business hours regardless of storage timezone
SELECT 
    toHour(toTimeZone(event_time, 'Asia/Tokyo')) AS tokyo_hour,
    count() AS events
FROM user_events 
GROUP BY tokyo_hour
HAVING tokyo_hour BETWEEN 9 AND 17;

Always use explicit timezone conversion for user-facing analytics where local time matters. Your users in Tokyo don't want to see their 3 PM meeting scheduled at 6 AM because of timezone confusion.

Converting a string to datetime then extracting parts

Many datasets store timestamps as strings, which means you need to convert them to ClickHouse datetime types before extracting components. The conversion approach depends on your string format and how much precision you want.

ClickHouse string to datetime one-liner

parseDateTime() handles most common string formats automatically:

SELECT parseDateTime('2024-03-15 14:30:45') AS parsed_dt;
-- Returns: 2024-03-15 14:30:45

SELECT parseDateTime('15/03/2024 2:30:45 PM', '%d/%m/%Y %h:%i:%s %p') AS custom_format;
-- Returns: 2024-03-15 14:30:45

For ISO 8601 strings with sub-second precision, use parseDateTime64():

SELECT parseDateTime64('2024-03-15T14:30:45.123Z', 3) AS parsed_dt64;
-- Returns: 2024-03-15 14:30:45.123

toDateTime() works for Unix timestamps and simple string formats:

SELECT toDateTime('1710506445') AS from_unix;
-- Returns: 2024-03-15 14:30:45

The format string uses MySQL-style format specifiers: %Y for four-digit year, %m for month, %d for day, %H for 24-hour format hour, %i for minutes, %s for seconds, and %p for AM/PM.

Combined cast and extract example

Here's a complete workflow from string parsing to component extraction:

WITH parsed_data AS (
    SELECT parseDateTime64('2024-03-15T14:30:45.123Z', 3) AS event_time
)
SELECT 
    toYear(event_time) AS year,
    toQuarter(event_time) AS quarter,
    toMonth(event_time) AS month,
    toDayOfMonth(event_time) AS day,
    toHour(event_time) AS hour,
    toMinute(event_time) AS minute,
    toSecond(event_time) AS second,
    toMillisecond(event_time) AS millisecond
FROM parsed_data;

This pattern works well for ETL processes where you need to extract multiple time components from string data:

-- Transform log timestamps into analytics dimensions
SELECT 
    log_message,
    toYear(parseDateTime(timestamp_str)) AS year,
    toMonth(parseDateTime(timestamp_str)) AS month,
    toDayOfWeek(parseDateTime(timestamp_str)) AS weekday
FROM raw_logs
WHERE timestamp_str != '';

Parse strings once and store them as proper datetime columns rather than parsing repeatedly in queries. Repeated parsing slows down your queries and wastes CPU cycles.

Performance tips for high-volume time extracts

Time component extraction on large datasets benefits from specific optimization approaches. Skip indexes and pre-aggregation reduce query execution time and resource usage when processing millions or billions of rows.

Using skip indexes on date columns

Minmax skip indexes on date columns dramatically improve query performance when filtering by extracted time components:

-- Create table with skip index on date column
CREATE TABLE events (
    event_date Date,
    event_timestamp DateTime,
    user_id UInt64,
    event_type String,
    INDEX idx_date event_date TYPE minmax GRANULARITY 1
) ENGINE = MergeTree 
ORDER BY (event_date, user_id);

The skip index allows ClickHouse to skip entire data blocks when filtering by date ranges or extracted components:

-- This query benefits from the skip index
SELECT count() 
FROM events 
WHERE toMonth(event_date) = 3 
  AND toYear(event_date) = 2024;

Skip indexes work particularly well with time-based partitioning:

-- Partition by month for even better performance
CREATE TABLE monthly_events (
    event_timestamp DateTime,
    user_id UInt64,
    event_data String
) ENGINE = MergeTree 
PARTITION BY toYYYYMM(event_timestamp)
ORDER BY (event_timestamp, user_id);

The combination of partitioning and skip indexes gives you the best performance for time-based queries. ClickHouse can eliminate entire partitions from consideration before even looking at the skip indexes.

Pre-aggregating time buckets

Materialized views can pre-calculate common time extractions, eliminating repeated computation:

-- Base events table
CREATE TABLE events (
    timestamp DateTime,
    user_id UInt64,
    event_type String,
    value Float64
) ENGINE = MergeTree ORDER BY timestamp;

-- Pre-aggregated hourly metrics
CREATE MATERIALIZED VIEW hourly_metrics
ENGINE = SummingMergeTree ORDER BY (date, hour, event_type)
AS SELECT 
    toDate(timestamp) AS date,
    toHour(timestamp) AS hour,
    event_type,
    count() AS event_count,
    sum(value) AS total_value
FROM events
GROUP BY date, hour, event_type;

Queries against the materialized view run orders of magnitude faster:

-- Fast query against pre-aggregated data
SELECT hour, sum(event_count) AS total_events
FROM hourly_metrics 
WHERE date = '2024-03-15'
GROUP BY hour
ORDER BY hour;

Materialized views use additional storage but provide significant query performance improvements for common time-based analytics. The trade-off usually makes sense when you're running the same time-based aggregations repeatedly.

Building ClickHouse-based analytics APIs with Tinybird

Let's create a complete example that demonstrates how to build a real-time API for time component extraction using Tinybird's managed ClickHouse platform.

This walkthrough shows how to ingest time-series data, process timestamps with ClickHouse functions, and expose time-based analytics through a real-time API endpoint.

1. Create the data source

First, create a data source to store time-series events:

SCHEMA >
    `timestamp` DateTime64(3) `json:$.timestamp`,
    `user_id` String `json:$.user_id`,
    `event_type` String `json:$.event_type`,
    `value` Float64 `json:$.value`,
    `session_id` String `json:$.session_id`

ENGINE MergeTree
ENGINE_SORTING_KEY timestamp
ENGINE_PARTITION_KEY toYYYYMM(timestamp)

Now build the project locally:

tb dev

This builds the project on Tinybird Local and creates your data source. Then ingest some sample data using the Events API:

curl -X POST "http://localhost:7181/v0/events?name=time_series_events" \
  -H "Content-Type: application/json" \
  -d '{"timestamp": "2025-01-15 14:30:15.123", "user_id": "user123", "event_type": "page_view", "value": 1.0, "session_id": "sess_abc123"}
{"timestamp": "2025-01-15 14:30:16.456", "user_id": "user456", "event_type": "click", "value": 2.5, "session_id": "sess_def456"}
{"timestamp": "2025-01-15 14:30:17.789", "user_id": "user789", "event_type": "conversion", "value": 10.0, "session_id": "sess_ghi789"}'

2. Create the pipe files

Create a pipe that processes time data and provides analytics:

NODE time_events
SQL >
    SELECT
        timestamp,
        user_id,
        event_type,
        value,
        session_id,
        toYear(timestamp) AS year,
        toQuarter(timestamp) AS quarter,
        toMonth(timestamp) AS month,
        toDayOfMonth(timestamp) AS day,
        toHour(timestamp) AS hour,
        toMinute(timestamp) AS minute,
        toSecond(timestamp) AS second,
        toMillisecond(timestamp) AS millisecond
    FROM time_series_events
    WHERE timestamp >= now64(3) - INTERVAL 1 HOUR

NODE time_analytics
SQL >
    %
    SELECT
        year,
        quarter,
        month,
        day,
        hour,
        event_type,
        COUNT(*) AS event_count,
        COUNT(DISTINCT user_id) AS unique_users,
        AVG(value) AS avg_value,
        MIN(timestamp) AS first_event,
        MAX(timestamp) AS last_event
    FROM time_events
    WHERE 
        {\% if defined(event_type) %}
            event_type = {{ String(event_type) }}
        {\% end %}
        {\% if defined(year) %}
            AND year = {{ Int32(year) }}
        {\% end %}
        {\% if defined(hours_back) %}
            AND timestamp >= now64(3) - INTERVAL {{ Int32(hours_back) }} HOUR
        {\% end %}
    GROUP BY year, quarter, month, day, hour, event_type
    ORDER BY year DESC, quarter DESC, month DESC, day DESC, hour DESC
    LIMIT {{ Int32(limit, 100) }}

TYPE endpoint

3. Deploy and test the API

Deploy your project to Tinybird Cloud:

tb --cloud deploy

Then test your API endpoint:

# Get all time analytics from the last hour
curl -H "Authorization: Bearer <your_token>" \
  "https://api.tinybird.co/v0/pipes/time_analytics.json"

# Filter by event type
curl -H "Authorization: Bearer <your_token>" \
  "https://api.tinybird.co/v0/pipes/time_analytics.json?event_type=page_view"

# Filter by year and time range
curl -H "Authorization: Bearer <your_token>" \
  "https://api.tinybird.co/v0/pipes/time_analytics.json?year=2025&hours_back=2"

4. Sample API response

The API returns structured data with time component analytics:

{
  "meta": [
    {
      "name": "year",
      "type": "UInt16"
    },
    {
      "name": "quarter",
      "type": "UInt8"
    },
    {
      "name": "month",
      "type": "UInt8"
    },
    {
      "name": "day",
      "type": "UInt8"
    },
    {
      "name": "hour",
      "type": "UInt8"
    },
    {
      "name": "event_type",
      "type": "String"
    },
    {
      "name": "event_count",
      "type": "UInt64"
    },
    {
      "name": "unique_users",
      "type": "UInt64"
    },
    {
      "name": "avg_value",
      "type": "Float64"
    },
    {
      "name": "first_event",
      "type": "DateTime64(3)"
    },
    {
      "name": "last_event",
      "type": "DateTime64(3)"
    }
  ],
  "data": [
    {
      "year": 2025,
      "quarter": 1,
      "month": 1,
      "day": 15,
      "hour": 14,
      "event_type": "page_view",
      "event_count": 1247,
      "unique_users": 892,
      "avg_value": 1.0,
      "first_event": "2025-01-15 14:30:15.123",
      "last_event": "2025-01-15 14:59:58.456"
    },
    {
      "year": 2025,
      "quarter": 1,
      "month": 1,
      "day": 15,
      "hour": 14,
      "event_type": "click",
      "event_count": 456,
      "unique_users": 234,
      "avg_value": 2.5,
      "first_event": "2025-01-15 14:30:16.456",
      "last_event": "2025-01-15 14:45:22.789"
    }
  ],
  "rows": 2,
  "statistics": {
    "elapsed": 0.045,
    "rows_read": 2000,
    "bytes_read": 89000
  }
}

Next steps with Tinybird's managed ClickHouse

Managing ClickHouse infrastructure for time component extraction involves complexity—from optimizing storage and query performance to handling timezone edge cases and maintaining consistent precision across distributed systems.

Tinybird's managed ClickHouse service reduces infrastructure overhead by providing a managed ClickHouse environment that abstracts optimization, scaling, and maintenance. Tinybird exposes ClickHouse features such as toYear(), toMonth(), and toHour() without requiring direct infrastructure management, including database clusters, performance monitoring, or version upgrades.

This enables teams to focus on application development rather than database operations or configuring ClickHouse for production workloads. Tinybird's platform supports developer efficiency through managed scaling and workflow integrations.

Sign up for a free Tinybird account to build and test your first ClickHouse-based API in just a few minutes.

Additional resources

• toYear function
• toMonth function
• toHour function
• toDayOfMonth function
• toDayOfWeek function
• toWeek function
• toMinute function
• toSecond function
• toMillisecond function
• DateTime64 data type
• How to get the current timestamp with sub-second precision in ClickHouse
• ClickHouse round date functions
• Convert datetime to YYYYMM in ClickHouse

FAQs about time extraction in ClickHouse

What is the function for ISO week numbers?

Use toISOWeek() to get ISO 8601 week numbers (1-53) where week 1 contains the first Thursday of the year. This differs from toWeek() which has configurable modes and may return different results for year-boundary weeks.

Why does toMillisecond return zero with DateTime?

DateTime types only store second-level precision, so toMillisecond() always returns zero. You need DateTime64 with explicit precision (like DateTime64(3) for milliseconds) to extract meaningful sub-second values.

Can I extract microseconds or nanoseconds?

ClickHouse supports microsecond precision with DateTime64(6) and nanosecond precision with DateTime64(9). However, there's no built-in toMicrosecond() function, so you'll need to use modulo arithmetic on the DateTime64 value to extract microsecond or nanosecond components.