---
title: "How to extract year, month, day, and time components from dates in ClickHouse®"
excerpt: "Learn how to extract year, month, day, hour, and other time components from ClickHouse® dates using toYear(), toMonth(), toHour() and more built-in functions."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-30 17:20:03"
publishedOn: "2025-10-03 10:30:00"
updatedOn: "2025-10-03 10:30:00"
status: "published"
---

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](https://www.tinybird.co) managed ClickHouse® platform.

## Time extraction functions in ClickHouse®

ClickHouse® gives you dedicated functions to pull specific time pieces from [Date](https://www.tinybird.co/docs/sql-reference/data-types/date), [DateTime](https://www.tinybird.co/docs/sql-reference/data-types/datetime), and [DateTime64](https://www.tinybird.co/docs/sql-reference/data-types/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:

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

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

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

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

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

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

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

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

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

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

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

The time extraction functions work with DateTime and DateTime64 values:

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

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

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

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

| Function | Date Support | DateTime Support | Example Output |
| -------- | ------------ | ----------------- | --------------- |
| toYear() | ✓ | ✓ | 2024 |
| toMonth() | ✓ | ✓ | 12 |
| toHour() | ✓ (returns 0) | ✓ | 14 |
| toMinute() | ✓ (returns 0) | ✓ | 30 |
| toSecond() | ✓ (returns 0) | ✓ | 45 |

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:

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

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

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

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

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

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

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

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

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

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

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

```sql
-- 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](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views) can pre-calculate common time extractions, eliminating repeated computation:

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

```sql
-- 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](https://www.tinybird.co) 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:

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

```bash
tb dev
```

This builds the project on [Tinybird Local](https://www.tinybird.co/docs/forward/install-tinybird/local) and creates your data source. Then ingest some sample data using the Events API:

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

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

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

Then test your API endpoint:

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

```json
{
  "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](https://www.tinybird.co/clickhouse) 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](https://cloud.tinybird.co/signup) to build and test your first ClickHouse®-based API in just a few minutes.

## Additional resources

- [toYear function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#toyear)
- [toMonth function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tomonth)
- [toHour function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tohour)
- [toDayOfMonth function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todayofmonth)
- [toDayOfWeek function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todayofweek)
- [toWeek function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#toweek)
- [toMinute function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tominute)
- [toSecond function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tosecond)
- [toMillisecond function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tomillisecond)
- [DateTime64 data type](https://clickhouse.com/docs/en/sql-reference/data-types/datetime64)
- [How to get the current timestamp with sub-second precision in ClickHouse®](https://www.tinybird.co/blog-posts/current-timestamp-sub-second-clickhouse)
- [ClickHouse® round date functions](https://www.tinybird.co/blog-posts/clickhouse-round-date-functions)
- [Convert datetime to YYYYMM in ClickHouse®](https://www.tinybird.co/blog-posts/convert-datetime-to-yyyymm-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.