🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa168)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical, ml_categories_json AS ml_categories_json, ml_types_json AS ml_types_json, ml_intent_types_json AS ml_intent_types_json, meta_language AS meta_language, attrs_author AS attrs_author, ifNull(toUnixTimestamp(attrs_publish_time), 0) AS attrs_publish_time, ifNull(toUnixTimestamp(attrs_original_publish_time), 0) AS attrs_original_publish_time, ifNull(attrs_is_republished, 0) AS attrs_is_republished, ifNull(attrs_nr_words, 0) AS attrs_nr_words, ifNull(attrs_boilerpipe_nr_words, 0) AS attrs_boilerpipe_nr_words, ifNull(body_ext_links_number, 0) AS body_ext_links_number, ifNull(body_int_links_number, 0) AS body_int_links_number, ifNull(meta_nofollow, 0) AS meta_nofollow, ifNull(meta_noarchive, 0) AS meta_noarchive, ifNull(props_was_rendered, 0) AS props_was_rendered, ifNull(src_redirect, \'\') AS src_redirect, ifNull(download_time_msec, 0) AS download_time_msec, ifNull(download_ttfb_msec, 0) AS download_ttfb_msec, ifNull(download_size, 0) AS download_size FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/use-cases/time-series/basic-operations\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/use-cases/time-series/basic-operations\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations","crawl_time":1775273862,"first_indexed_time":1745698149,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/use-cases\/time-series\/basic-operations s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"Basic operations - time-series | ClickHouse Docs","meta_descriptions":["Basic time-series operations in ClickHouse."],"attrs_boilerpipe_text":"ClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods.\nThis section covers the fundamental operations commonly used when working with time-based data.\nCommon operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods.\nThese operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions.\nWe're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset:\nCREATE TABLE wikistat\n(\n    `time` DateTime,\n    `project` String,\n    `subproject` String,\n    `path` String,\n    `hits` UInt64\n)\nENGINE = MergeTree\nORDER BY (time);\nLet's populate this table with 1 billion records:\nINSERT INTO wikistat \nSELECT *\nFROM s3('https:\/\/ClickHouse-public-datasets.s3.amazonaws.com\/wikistat\/partitioned\/wikistat*.native.zst') \nLIMIT 1e9;\nAggregating by time bucket\n\nThe most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day:\nSELECT\n    toDate(time) AS date,\n    sum(hits) AS hits\nFROM wikistat\nGROUP BY ALL\nORDER BY date ASC\nLIMIT 5;\n┌───────date─┬─────hits─┐\n│ 2015-05-01 │ 25524369 │\n│ 2015-05-02 │ 25608105 │\n│ 2015-05-03 │ 28567101 │\n│ 2015-05-04 │ 29229944 │\n│ 2015-05-05 │ 29383573 │\n└────────────┴──────────┘\nWe've used the\ntoDate()\nfunction here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date:\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits) AS hits    \nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY hour ASC\nLIMIT 5;\n┌────────────────hour─┬───hits─┐\n│ 2015-07-01 00:00:00 │ 656676 │\n│ 2015-07-01 01:00:00 │ 768837 │\n│ 2015-07-01 02:00:00 │ 862311 │\n│ 2015-07-01 03:00:00 │ 829261 │\n│ 2015-07-01 04:00:00 │ 749365 │\n└─────────────────────┴────────┘\nThe\ntoStartOfHour()\nfunction used here converts the given time to the start of the hour.\nYou can also group by year, quarter, month, or day.\nCustom grouping intervals\n\nWe can even group by arbitrary intervals, e.g., 5 minutes using the\ntoStartOfInterval()\nfunction.\nLet's say we want to group by 4-hour intervals.\nWe can specify the grouping interval using the\nINTERVAL\nclause:\nSELECT\n    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\nOr we can use the\ntoIntervalHour()\nfunction\nSELECT\n    toStartOfInterval(time, toIntervalHour(4)) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\nEither way, we get the following results:\n┌────────────interval─┬────hits─┐\n│ 2015-07-01 00:00:00 │ 3117085 │\n│ 2015-07-01 04:00:00 │ 2928396 │\n│ 2015-07-01 08:00:00 │ 2679775 │\n│ 2015-07-01 12:00:00 │ 2461324 │\n│ 2015-07-01 16:00:00 │ 2823199 │\n│ 2015-07-01 20:00:00 │ 2984758 │\n└─────────────────────┴─────────┘\nFilling empty groups\n\nIn a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values:\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC;\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │ <- missing values\n│ 2015-07-01 02:00:00 │         1 │ <- missing values\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │ <- missing values\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\nClickHouse provides the\nWITH FILL\nmodifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time:\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC WITH FILL STEP toIntervalHour(1);\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │\n│ 2015-07-01 01:00:00 │         0 │ <- new value\n│ 2015-07-01 02:00:00 │         1 │\n│ 2015-07-01 03:00:00 │         0 │ <- new value\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │\n│ 2015-07-01 10:00:00 │         0 │ <- new value\n│ 2015-07-01 11:00:00 │         0 │ <- new value\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\nRolling time windows\n\nSometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals.\nLet's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm.\nWe can use the\ndate_diff()\nfunction to calculate the difference between a reference time and each record's time.\nIn this case, the\nday\ncolumn will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.):\nSELECT    \n    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,\n    sum(hits),\nFROM wikistat\nGROUP BY ALL\nORDER BY day ASC\nLIMIT 5;\n┌─day─┬─sum(hits)─┐\n│   0 │  25524369 │\n│   1 │  25608105 │\n│   2 │  28567101 │\n│   3 │  29229944 │\n│   4 │  29383573 │\n└─────┴───────────┘","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs_dark.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n\n- [English](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/use-cases\/time-series\/basic-operations)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/use-cases\/time-series\/basic-operations)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/use-cases\/time-series\/basic-operations)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/use-cases\/time-series\/basic-operations)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs_dark.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n\n- [English](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/use-cases\/time-series\/basic-operations)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/use-cases\/time-series\/basic-operations)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/use-cases\/time-series\/basic-operations)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/use-cases\/time-series\/basic-operations)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n- [Concepts](https:\/\/clickhouse.com\/docs\/concepts)\n  - [What is OLAP?](https:\/\/clickhouse.com\/docs\/concepts\/olap)\n  - [Why is ClickHouse so fast?](https:\/\/clickhouse.com\/docs\/concepts\/why-clickhouse-is-so-fast)\n  - [Why is ClickHouse unique?](https:\/\/clickhouse.com\/docs\/about-us\/distinctive-features)\n  - [Glossary](https:\/\/clickhouse.com\/docs\/concepts\/glossary)\n  - [FAQ](https:\/\/clickhouse.com\/docs\/concepts\/faq)\n- [Starter guides](https:\/\/clickhouse.com\/docs\/starter-guides)\n  - [Creating tables](https:\/\/clickhouse.com\/docs\/guides\/creating-tables)\n  - [Updating and deleting data](https:\/\/clickhouse.com\/docs\/guides\/developer\/mutations)\n  - [Selecting data](https:\/\/clickhouse.com\/docs\/guides\/writing-queries)\n  - [Generating random test data](https:\/\/clickhouse.com\/docs\/guides\/generating-test-data)\n  - [Inserting ClickHouse data](https:\/\/clickhouse.com\/docs\/guides\/inserting-data)\n  - [Working with arrays in ClickHouse](https:\/\/clickhouse.com\/docs\/guides\/working-with-arrays)\n  - [Working with JOINs in ClickHouse](https:\/\/clickhouse.com\/docs\/guides\/working-with-joins)\n- [Troubleshooting](https:\/\/clickhouse.com\/docs\/troubleshooting)\n- [Best practices](https:\/\/clickhouse.com\/docs\/best-practices)\n  - [Sizing and hardware recommendations](https:\/\/clickhouse.com\/docs\/guides\/sizing-and-hardware-recommendations)\n  - [Choosing a primary key](https:\/\/clickhouse.com\/docs\/best-practices\/choosing-a-primary-key)\n  - [Selecting data types](https:\/\/clickhouse.com\/docs\/best-practices\/select-data-types)\n  - [Use materialized views](https:\/\/clickhouse.com\/docs\/best-practices\/use-materialized-views)\n  - [Minimize and optimize JOINs](https:\/\/clickhouse.com\/docs\/best-practices\/minimize-optimize-joins)\n  - [Choosing a partitioning key](https:\/\/clickhouse.com\/docs\/best-practices\/choosing-a-partitioning-key)\n  - [Selecting an insert strategy](https:\/\/clickhouse.com\/docs\/best-practices\/selecting-an-insert-strategy)\n  - [Data skipping indices](https:\/\/clickhouse.com\/docs\/best-practices\/use-data-skipping-indices-where-appropriate)\n  - [Avoid mutations](https:\/\/clickhouse.com\/docs\/best-practices\/avoid-mutations)\n  - [Avoid optimize final](https:\/\/clickhouse.com\/docs\/best-practices\/avoid-optimize-final)\n  - [Using JSON](https:\/\/clickhouse.com\/docs\/best-practices\/use-json-where-appropriate)\n- [Use case guides](https:\/\/clickhouse.com\/docs\/use-cases)\n  - [Observability](https:\/\/clickhouse.com\/docs\/use-cases\/observability)\n  - [Time-series](https:\/\/clickhouse.com\/docs\/use-cases\/time-series)\n    - [Date and time data types](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/date-time-data-types)\n    - [Basic operations](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations)\n    - [Analysis functions](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/analysis-functions)\n    - [Storage efficiency](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/storage-efficiency)\n    - [Query performance](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/query-performance)\n    - [Build a rollup with materialized views for fast time-series analytics](https:\/\/clickhouse.com\/docs\/knowledgebase\/materialized-view-rollup-timeseries)\n  - [Data lake](https:\/\/clickhouse.com\/docs\/use-cases\/data-lake)\n  - [AI\/ML](https:\/\/clickhouse.com\/docs\/use-cases\/AI\/ask-ai)\n- [Example datasets](https:\/\/clickhouse.com\/docs\/getting-started\/example-datasets)\n- [Tips and community wisdom](https:\/\/clickhouse.com\/docs\/tips-and-tricks\/community-wisdom)\n\n- [Use case guides](https:\/\/clickhouse.com\/docs\/use-cases)\n- [Time-series](https:\/\/clickhouse.com\/docs\/use-cases\/time-series)\n- Basic operations\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/clickhouse-docs\/blob\/main\/docs\/use-cases\/time-series\/02_basic-operations.md)\n\n# Basic time-series operations\nClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods. This section covers the fundamental operations commonly used when working with time-based data.\n\nCommon operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods. These operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions.\n\nWe're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset:\n```\nCREATE TABLE wikistat\n(\n    `time` DateTime,\n    `project` String,\n    `subproject` String,\n    `path` String,\n    `hits` UInt64\n)\nENGINE = MergeTree\nORDER BY (time);\n```\nLet's populate this table with 1 billion records:\n```\nINSERT INTO wikistat \nSELECT *\nFROM s3('https:\/\/ClickHouse-public-datasets.s3.amazonaws.com\/wikistat\/partitioned\/wikistat*.native.zst') \nLIMIT 1e9;\n```\n## Aggregating by time bucket[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-aggregating-time-bucket \"Direct link to Aggregating by time bucket\")\nThe most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day:\n```\nSELECT\n    toDate(time) AS date,\n    sum(hits) AS hits\nFROM wikistat\nGROUP BY ALL\nORDER BY date ASC\nLIMIT 5;\n```\n```\n┌───────date─┬─────hits─┐\n│ 2015-05-01 │ 25524369 │\n│ 2015-05-02 │ 25608105 │\n│ 2015-05-03 │ 28567101 │\n│ 2015-05-04 │ 29229944 │\n│ 2015-05-05 │ 29383573 │\n└────────────┴──────────┘\n```\nWe've used the [`toDate()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/type-conversion-functions#toDate) function here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits) AS hits    \nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY hour ASC\nLIMIT 5;\n```\n```\n┌────────────────hour─┬───hits─┐\n│ 2015-07-01 00:00:00 │ 656676 │\n│ 2015-07-01 01:00:00 │ 768837 │\n│ 2015-07-01 02:00:00 │ 862311 │\n│ 2015-07-01 03:00:00 │ 829261 │\n│ 2015-07-01 04:00:00 │ 749365 │\n└─────────────────────┴────────┘\n```\nThe [`toStartOfHour()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#toStartOfHour) function used here converts the given time to the start of the hour. You can also group by year, quarter, month, or day.\n\n## Custom grouping intervals[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-custom-grouping-intervals \"Direct link to Custom grouping intervals\")\nWe can even group by arbitrary intervals, e.g., 5 minutes using the [`toStartOfInterval()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#toStartOfInterval) function.\n\nLet's say we want to group by 4-hour intervals. We can specify the grouping interval using the [`INTERVAL`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/special-data-types\/interval) clause:\n```\nSELECT\n    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\n```\nOr we can use the [`toIntervalHour()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/type-conversion-functions#toIntervalHour) function\n```\nSELECT\n    toStartOfInterval(time, toIntervalHour(4)) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\n```\nEither way, we get the following results:\n```\n┌────────────interval─┬────hits─┐\n│ 2015-07-01 00:00:00 │ 3117085 │\n│ 2015-07-01 04:00:00 │ 2928396 │\n│ 2015-07-01 08:00:00 │ 2679775 │\n│ 2015-07-01 12:00:00 │ 2461324 │\n│ 2015-07-01 16:00:00 │ 2823199 │\n│ 2015-07-01 20:00:00 │ 2984758 │\n└─────────────────────┴─────────┘\n```\n## Filling empty groups[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-filling-empty-groups \"Direct link to Filling empty groups\")\nIn a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC;\n```\n```\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │ <- missing values\n│ 2015-07-01 02:00:00 │         1 │ <- missing values\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │ <- missing values\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\n```\nClickHouse provides the [`WITH FILL`](https:\/\/clickhouse.com\/docs\/guides\/developer\/time-series-filling-gaps#with-fill) modifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC WITH FILL STEP toIntervalHour(1);\n```\n```\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │\n│ 2015-07-01 01:00:00 │         0 │ <- new value\n│ 2015-07-01 02:00:00 │         1 │\n│ 2015-07-01 03:00:00 │         0 │ <- new value\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │\n│ 2015-07-01 10:00:00 │         0 │ <- new value\n│ 2015-07-01 11:00:00 │         0 │ <- new value\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\n```\n## Rolling time windows[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-rolling-time-windows \"Direct link to Rolling time windows\")\nSometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals. Let's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm.\n\nWe can use the [`date_diff()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#timeDiff) function to calculate the difference between a reference time and each record's time. In this case, the `day` column will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.):\n```\nSELECT    \n    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,\n    sum(hits),\nFROM wikistat\nGROUP BY ALL\nORDER BY day ASC\nLIMIT 5;\n```\n```\n┌─day─┬─sum(hits)─┐\n│   0 │  25524369 │\n│   1 │  25608105 │\n│   2 │  28567101 │\n│   3 │  29229944 │\n│   4 │  29383573 │\n└─────┴───────────┘\n```\n\n[Previous Date and time data types](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/date-time-data-types)\n\n[Next Analysis functions](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/analysis-functions)\n\n- [Aggregating by time bucket](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-aggregating-time-bucket)\n- [Custom grouping intervals](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-custom-grouping-intervals)\n- [Filling empty groups](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-filling-empty-groups)\n- [Rolling time windows](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-rolling-time-windows)\n\nWas this page helpful?\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)","attrs_readable_markdown":"ClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods. This section covers the fundamental operations commonly used when working with time-based data.\n\nCommon operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods. These operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions.\n\nWe're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset:\n```\nCREATE TABLE wikistat\n(\n    `time` DateTime,\n    `project` String,\n    `subproject` String,\n    `path` String,\n    `hits` UInt64\n)\nENGINE = MergeTree\nORDER BY (time);\n```\nLet's populate this table with 1 billion records:\n```\nINSERT INTO wikistat \nSELECT *\nFROM s3('https:\/\/ClickHouse-public-datasets.s3.amazonaws.com\/wikistat\/partitioned\/wikistat*.native.zst') \nLIMIT 1e9;\n```\n## Aggregating by time bucket[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-aggregating-time-bucket \"Direct link to Aggregating by time bucket\")\nThe most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day:\n```\nSELECT\n    toDate(time) AS date,\n    sum(hits) AS hits\nFROM wikistat\nGROUP BY ALL\nORDER BY date ASC\nLIMIT 5;\n```\n```\n┌───────date─┬─────hits─┐\n│ 2015-05-01 │ 25524369 │\n│ 2015-05-02 │ 25608105 │\n│ 2015-05-03 │ 28567101 │\n│ 2015-05-04 │ 29229944 │\n│ 2015-05-05 │ 29383573 │\n└────────────┴──────────┘\n```\nWe've used the [`toDate()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/type-conversion-functions#toDate) function here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits) AS hits    \nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY hour ASC\nLIMIT 5;\n```\n```\n┌────────────────hour─┬───hits─┐\n│ 2015-07-01 00:00:00 │ 656676 │\n│ 2015-07-01 01:00:00 │ 768837 │\n│ 2015-07-01 02:00:00 │ 862311 │\n│ 2015-07-01 03:00:00 │ 829261 │\n│ 2015-07-01 04:00:00 │ 749365 │\n└─────────────────────┴────────┘\n```\nThe [`toStartOfHour()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#toStartOfHour) function used here converts the given time to the start of the hour. You can also group by year, quarter, month, or day.\n\n## Custom grouping intervals[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-custom-grouping-intervals \"Direct link to Custom grouping intervals\")\nWe can even group by arbitrary intervals, e.g., 5 minutes using the [`toStartOfInterval()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#toStartOfInterval) function.\n\nLet's say we want to group by 4-hour intervals. We can specify the grouping interval using the [`INTERVAL`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/special-data-types\/interval) clause:\n```\nSELECT\n    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\n```\nOr we can use the [`toIntervalHour()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/type-conversion-functions#toIntervalHour) function\n```\nSELECT\n    toStartOfInterval(time, toIntervalHour(4)) AS interval,\n    sum(hits) AS hits\nFROM wikistat\nWHERE date(time) = '2015-07-01'\nGROUP BY ALL\nORDER BY interval ASC\nLIMIT 6;\n```\nEither way, we get the following results:\n```\n┌────────────interval─┬────hits─┐\n│ 2015-07-01 00:00:00 │ 3117085 │\n│ 2015-07-01 04:00:00 │ 2928396 │\n│ 2015-07-01 08:00:00 │ 2679775 │\n│ 2015-07-01 12:00:00 │ 2461324 │\n│ 2015-07-01 16:00:00 │ 2823199 │\n│ 2015-07-01 20:00:00 │ 2984758 │\n└─────────────────────┴─────────┘\n```\n## Filling empty groups[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-filling-empty-groups \"Direct link to Filling empty groups\")\nIn a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC;\n```\n```\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │ <- missing values\n│ 2015-07-01 02:00:00 │         1 │ <- missing values\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │ <- missing values\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\n```\nClickHouse provides the [`WITH FILL`](https:\/\/clickhouse.com\/docs\/guides\/developer\/time-series-filling-gaps#with-fill) modifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time:\n```\nSELECT\n    toStartOfHour(time) AS hour,\n    sum(hits)\nFROM wikistat\nWHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')\nGROUP BY ALL\nORDER BY hour ASC WITH FILL STEP toIntervalHour(1);\n```\n```\n┌────────────────hour─┬─sum(hits)─┐\n│ 2015-07-01 00:00:00 │         3 │\n│ 2015-07-01 01:00:00 │         0 │ <- new value\n│ 2015-07-01 02:00:00 │         1 │\n│ 2015-07-01 03:00:00 │         0 │ <- new value\n│ 2015-07-01 04:00:00 │         1 │\n│ 2015-07-01 05:00:00 │         2 │\n│ 2015-07-01 06:00:00 │         1 │\n│ 2015-07-01 07:00:00 │         1 │\n│ 2015-07-01 08:00:00 │         3 │\n│ 2015-07-01 09:00:00 │         2 │\n│ 2015-07-01 10:00:00 │         0 │ <- new value\n│ 2015-07-01 11:00:00 │         0 │ <- new value\n│ 2015-07-01 12:00:00 │         2 │\n│ 2015-07-01 13:00:00 │         4 │\n│ 2015-07-01 14:00:00 │         2 │\n│ 2015-07-01 15:00:00 │         2 │\n│ 2015-07-01 16:00:00 │         2 │\n│ 2015-07-01 17:00:00 │         1 │\n│ 2015-07-01 18:00:00 │         5 │\n│ 2015-07-01 19:00:00 │         5 │\n│ 2015-07-01 20:00:00 │         4 │\n│ 2015-07-01 21:00:00 │         4 │\n│ 2015-07-01 22:00:00 │         2 │\n│ 2015-07-01 23:00:00 │         2 │\n└─────────────────────┴───────────┘\n```\n## Rolling time windows[](https:\/\/clickhouse.com\/docs\/use-cases\/time-series\/basic-operations#time-series-rolling-time-windows \"Direct link to Rolling time windows\")\nSometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals. Let's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm.\n\nWe can use the [`date_diff()`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/date-time-functions#timeDiff) function to calculate the difference between a reference time and each record's time. In this case, the `day` column will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.):\n```\nSELECT    \n    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,\n    sum(hits),\nFROM wikistat\nGROUP BY ALL\nORDER BY day ASC\nLIMIT 5;\n```\n```\n┌─day─┬─sum(hits)─┐\n│   0 │  25524369 │\n│   1 │  25608105 │\n│   2 │  28567101 │\n│   3 │  29229944 │\n│   4 │  29383573 │\n└─────┴───────────┘\n```","meta_canonical":null,"ml_categories_json":"","ml_types_json":"","ml_intent_types_json":"","meta_language":"en","attrs_author":null,"attrs_publish_time":0,"attrs_original_publish_time":1745698149,"attrs_is_republished":0,"attrs_nr_words":"1431","attrs_boilerpipe_nr_words":"994","body_ext_links_number":5,"body_int_links_number":92,"meta_nofollow":0,"meta_noarchive":0,"props_was_rendered":0,"src_redirect":"","download_time_msec":204,"download_ttfb_msec":203,"download_size":13398}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

19 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.7 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/use-cases/time-series/basic-operations
Last Crawled	2026-04-04 03:37:42 (19 days ago)
First Indexed	2025-04-26 20:09:09 (12 months ago)
HTTP Status Code	200
Content
Meta Title	Basic operations - time-series \| ClickHouse Docs
Meta Description	Basic time-series operations in ClickHouse.
Meta Canonical	null
Boilerpipe Text	ClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods. This section covers the fundamental operations commonly used when working with time-based data. Common operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods. These operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions. We're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset: CREATE TABLE wikistat ( `time` DateTime, `project` String, `subproject` String, `path` String, `hits` UInt64 ) ENGINE = MergeTree ORDER BY (time); Let's populate this table with 1 billion records: INSERT INTO wikistat SELECT * FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1e9; Aggregating by time bucket The most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day: SELECT toDate(time) AS date, sum(hits) AS hits FROM wikistat GROUP BY ALL ORDER BY date ASC LIMIT 5; ┌───────date─┬─────hits─┐ │ 2015-05-01 │ 25524369 │ │ 2015-05-02 │ 25608105 │ │ 2015-05-03 │ 28567101 │ │ 2015-05-04 │ 29229944 │ │ 2015-05-05 │ 29383573 │ └────────────┴──────────┘ We've used the toDate() function here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date: SELECT toStartOfHour(time) AS hour, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY hour ASC LIMIT 5; ┌────────────────hour─┬───hits─┐ │ 2015-07-01 00:00:00 │ 656676 │ │ 2015-07-01 01:00:00 │ 768837 │ │ 2015-07-01 02:00:00 │ 862311 │ │ 2015-07-01 03:00:00 │ 829261 │ │ 2015-07-01 04:00:00 │ 749365 │ └─────────────────────┴────────┘ The toStartOfHour() function used here converts the given time to the start of the hour. You can also group by year, quarter, month, or day. Custom grouping intervals We can even group by arbitrary intervals, e.g., 5 minutes using the toStartOfInterval() function. Let's say we want to group by 4-hour intervals. We can specify the grouping interval using the INTERVAL clause: SELECT toStartOfInterval(time, INTERVAL 4 HOUR) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; Or we can use the toIntervalHour() function SELECT toStartOfInterval(time, toIntervalHour(4)) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; Either way, we get the following results: ┌────────────interval─┬────hits─┐ │ 2015-07-01 00:00:00 │ 3117085 │ │ 2015-07-01 04:00:00 │ 2928396 │ │ 2015-07-01 08:00:00 │ 2679775 │ │ 2015-07-01 12:00:00 │ 2461324 │ │ 2015-07-01 16:00:00 │ 2823199 │ │ 2015-07-01 20:00:00 │ 2984758 │ └─────────────────────┴─────────┘ Filling empty groups In a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values: SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC; ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ <- missing values │ 2015-07-01 02:00:00 │ 1 │ <- missing values │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ <- missing values │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ ClickHouse provides the WITH FILL modifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time: SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC WITH FILL STEP toIntervalHour(1); ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ │ 2015-07-01 01:00:00 │ 0 │ <- new value │ 2015-07-01 02:00:00 │ 1 │ │ 2015-07-01 03:00:00 │ 0 │ <- new value │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ │ 2015-07-01 10:00:00 │ 0 │ <- new value │ 2015-07-01 11:00:00 │ 0 │ <- new value │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ Rolling time windows Sometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals. Let's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm. We can use the date_diff() function to calculate the difference between a reference time and each record's time. In this case, the day column will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.): SELECT dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day, sum(hits), FROM wikistat GROUP BY ALL ORDER BY day ASC LIMIT 5; ┌─day─┬─sum(hits)─┐ │ 0 │ 25524369 │ │ 1 │ 25608105 │ │ 2 │ 28567101 │ │ 3 │ 29229944 │ │ 4 │ 29383573 │ └─────┴───────────┘
Markdown	[Skip to main content](https://clickhouse.com/docs/use-cases/time-series/basic-operations#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs_dark.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [English](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [日本語](https://clickhouse.com/docs/jp/use-cases/time-series/basic-operations) - [中文](https://clickhouse.com/docs/zh/use-cases/time-series/basic-operations) - [Русский](https://clickhouse.com/docs/ru/use-cases/time-series/basic-operations) - [한국어](https://clickhouse.com/docs/ko/use-cases/time-series/basic-operations) [Skip to main content](https://clickhouse.com/docs/use-cases/time-series/basic-operations#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs_dark.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [English](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [日本語](https://clickhouse.com/docs/jp/use-cases/time-series/basic-operations) - [中文](https://clickhouse.com/docs/zh/use-cases/time-series/basic-operations) - [Русский](https://clickhouse.com/docs/ru/use-cases/time-series/basic-operations) - [한국어](https://clickhouse.com/docs/ko/use-cases/time-series/basic-operations) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/introduction-clickhouse) - [Concepts](https://clickhouse.com/docs/concepts) - [What is OLAP?](https://clickhouse.com/docs/concepts/olap) - [Why is ClickHouse so fast?](https://clickhouse.com/docs/concepts/why-clickhouse-is-so-fast) - [Why is ClickHouse unique?](https://clickhouse.com/docs/about-us/distinctive-features) - [Glossary](https://clickhouse.com/docs/concepts/glossary) - [FAQ](https://clickhouse.com/docs/concepts/faq) - [Starter guides](https://clickhouse.com/docs/starter-guides) - [Creating tables](https://clickhouse.com/docs/guides/creating-tables) - [Updating and deleting data](https://clickhouse.com/docs/guides/developer/mutations) - [Selecting data](https://clickhouse.com/docs/guides/writing-queries) - [Generating random test data](https://clickhouse.com/docs/guides/generating-test-data) - [Inserting ClickHouse data](https://clickhouse.com/docs/guides/inserting-data) - [Working with arrays in ClickHouse](https://clickhouse.com/docs/guides/working-with-arrays) - [Working with JOINs in ClickHouse](https://clickhouse.com/docs/guides/working-with-joins) - [Troubleshooting](https://clickhouse.com/docs/troubleshooting) - [Best practices](https://clickhouse.com/docs/best-practices) - [Sizing and hardware recommendations](https://clickhouse.com/docs/guides/sizing-and-hardware-recommendations) - [Choosing a primary key](https://clickhouse.com/docs/best-practices/choosing-a-primary-key) - [Selecting data types](https://clickhouse.com/docs/best-practices/select-data-types) - [Use materialized views](https://clickhouse.com/docs/best-practices/use-materialized-views) - [Minimize and optimize JOINs](https://clickhouse.com/docs/best-practices/minimize-optimize-joins) - [Choosing a partitioning key](https://clickhouse.com/docs/best-practices/choosing-a-partitioning-key) - [Selecting an insert strategy](https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy) - [Data skipping indices](https://clickhouse.com/docs/best-practices/use-data-skipping-indices-where-appropriate) - [Avoid mutations](https://clickhouse.com/docs/best-practices/avoid-mutations) - [Avoid optimize final](https://clickhouse.com/docs/best-practices/avoid-optimize-final) - [Using JSON](https://clickhouse.com/docs/best-practices/use-json-where-appropriate) - [Use case guides](https://clickhouse.com/docs/use-cases) - [Observability](https://clickhouse.com/docs/use-cases/observability) - [Time-series](https://clickhouse.com/docs/use-cases/time-series) - [Date and time data types](https://clickhouse.com/docs/use-cases/time-series/date-time-data-types) - [Basic operations](https://clickhouse.com/docs/use-cases/time-series/basic-operations) - [Analysis functions](https://clickhouse.com/docs/use-cases/time-series/analysis-functions) - [Storage efficiency](https://clickhouse.com/docs/use-cases/time-series/storage-efficiency) - [Query performance](https://clickhouse.com/docs/use-cases/time-series/query-performance) - [Build a rollup with materialized views for fast time-series analytics](https://clickhouse.com/docs/knowledgebase/materialized-view-rollup-timeseries) - [Data lake](https://clickhouse.com/docs/use-cases/data-lake) - [AI/ML](https://clickhouse.com/docs/use-cases/AI/ask-ai) - [Example datasets](https://clickhouse.com/docs/getting-started/example-datasets) - [Tips and community wisdom](https://clickhouse.com/docs/tips-and-tricks/community-wisdom) - [Use case guides](https://clickhouse.com/docs/use-cases) - [Time-series](https://clickhouse.com/docs/use-cases/time-series) - Basic operations [Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/time-series/02_basic-operations.md) # Basic time-series operations ClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods. This section covers the fundamental operations commonly used when working with time-based data. Common operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods. These operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions. We're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset: ``` CREATE TABLE wikistat ( `time` DateTime, `project` String, `subproject` String, `path` String, `hits` UInt64 ) ENGINE = MergeTree ORDER BY (time); ``` Let's populate this table with 1 billion records: ``` INSERT INTO wikistat SELECT * FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1e9; ``` ## Aggregating by time bucket[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-aggregating-time-bucket "Direct link to Aggregating by time bucket") The most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day: ``` SELECT toDate(time) AS date, sum(hits) AS hits FROM wikistat GROUP BY ALL ORDER BY date ASC LIMIT 5; ``` ``` ┌───────date─┬─────hits─┐ │ 2015-05-01 │ 25524369 │ │ 2015-05-02 │ 25608105 │ │ 2015-05-03 │ 28567101 │ │ 2015-05-04 │ 29229944 │ │ 2015-05-05 │ 29383573 │ └────────────┴──────────┘ ``` We've used the [`toDate()`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#toDate) function here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date: ``` SELECT toStartOfHour(time) AS hour, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY hour ASC LIMIT 5; ``` ``` ┌────────────────hour─┬───hits─┐ │ 2015-07-01 00:00:00 │ 656676 │ │ 2015-07-01 01:00:00 │ 768837 │ │ 2015-07-01 02:00:00 │ 862311 │ │ 2015-07-01 03:00:00 │ 829261 │ │ 2015-07-01 04:00:00 │ 749365 │ └─────────────────────┴────────┘ ``` The [`toStartOfHour()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfHour) function used here converts the given time to the start of the hour. You can also group by year, quarter, month, or day. ## Custom grouping intervals[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-custom-grouping-intervals "Direct link to Custom grouping intervals") We can even group by arbitrary intervals, e.g., 5 minutes using the [`toStartOfInterval()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfInterval) function. Let's say we want to group by 4-hour intervals. We can specify the grouping interval using the [`INTERVAL`](https://clickhouse.com/docs/sql-reference/data-types/special-data-types/interval) clause: ``` SELECT toStartOfInterval(time, INTERVAL 4 HOUR) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; ``` Or we can use the [`toIntervalHour()`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#toIntervalHour) function ``` SELECT toStartOfInterval(time, toIntervalHour(4)) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; ``` Either way, we get the following results: ``` ┌────────────interval─┬────hits─┐ │ 2015-07-01 00:00:00 │ 3117085 │ │ 2015-07-01 04:00:00 │ 2928396 │ │ 2015-07-01 08:00:00 │ 2679775 │ │ 2015-07-01 12:00:00 │ 2461324 │ │ 2015-07-01 16:00:00 │ 2823199 │ │ 2015-07-01 20:00:00 │ 2984758 │ └─────────────────────┴─────────┘ ``` ## Filling empty groups[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-filling-empty-groups "Direct link to Filling empty groups") In a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values: ``` SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC; ``` ``` ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ <- missing values │ 2015-07-01 02:00:00 │ 1 │ <- missing values │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ <- missing values │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ ``` ClickHouse provides the [`WITH FILL`](https://clickhouse.com/docs/guides/developer/time-series-filling-gaps#with-fill) modifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time: ``` SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC WITH FILL STEP toIntervalHour(1); ``` ``` ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ │ 2015-07-01 01:00:00 │ 0 │ <- new value │ 2015-07-01 02:00:00 │ 1 │ │ 2015-07-01 03:00:00 │ 0 │ <- new value │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ │ 2015-07-01 10:00:00 │ 0 │ <- new value │ 2015-07-01 11:00:00 │ 0 │ <- new value │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ ``` ## Rolling time windows[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-rolling-time-windows "Direct link to Rolling time windows") Sometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals. Let's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm. We can use the [`date_diff()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#timeDiff) function to calculate the difference between a reference time and each record's time. In this case, the `day` column will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.): ``` SELECT dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day, sum(hits), FROM wikistat GROUP BY ALL ORDER BY day ASC LIMIT 5; ``` ``` ┌─day─┬─sum(hits)─┐ │ 0 │ 25524369 │ │ 1 │ 25608105 │ │ 2 │ 28567101 │ │ 3 │ 29229944 │ │ 4 │ 29383573 │ └─────┴───────────┘ ``` [Previous Date and time data types](https://clickhouse.com/docs/use-cases/time-series/date-time-data-types) [Next Analysis functions](https://clickhouse.com/docs/use-cases/time-series/analysis-functions) - [Aggregating by time bucket](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-aggregating-time-bucket) - [Custom grouping intervals](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-custom-grouping-intervals) - [Filling empty groups](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-filling-empty-groups) - [Rolling time windows](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-rolling-time-windows) Was this page helpful? © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)
Readable Markdown	ClickHouse provides several methods for working with time series data, allowing you to aggregate, group, and analyze data points across different time periods. This section covers the fundamental operations commonly used when working with time-based data. Common operations include grouping data by time intervals, handling gaps in time series data, and calculating changes between time periods. These operations can be performed using standard SQL syntax combined with ClickHouse's built-in time functions. We're going to explore ClickHouse time-series querying capabilities with the Wikistat (Wikipedia pageviews data) dataset: ``` CREATE TABLE wikistat ( `time` DateTime, `project` String, `subproject` String, `path` String, `hits` UInt64 ) ENGINE = MergeTree ORDER BY (time); ``` Let's populate this table with 1 billion records: ``` INSERT INTO wikistat SELECT * FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1e9; ``` ## Aggregating by time bucket[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-aggregating-time-bucket "Direct link to Aggregating by time bucket") The most popular requirement is to aggregate data based on periods, e.g. get the total amount of hits for each day: ``` SELECT toDate(time) AS date, sum(hits) AS hits FROM wikistat GROUP BY ALL ORDER BY date ASC LIMIT 5; ``` ``` ┌───────date─┬─────hits─┐ │ 2015-05-01 │ 25524369 │ │ 2015-05-02 │ 25608105 │ │ 2015-05-03 │ 28567101 │ │ 2015-05-04 │ 29229944 │ │ 2015-05-05 │ 29383573 │ └────────────┴──────────┘ ``` We've used the [`toDate()`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#toDate) function here, which converts the specified time to a date type. Alternatively, we can batch by an hour and filter on the specific date: ``` SELECT toStartOfHour(time) AS hour, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY hour ASC LIMIT 5; ``` ``` ┌────────────────hour─┬───hits─┐ │ 2015-07-01 00:00:00 │ 656676 │ │ 2015-07-01 01:00:00 │ 768837 │ │ 2015-07-01 02:00:00 │ 862311 │ │ 2015-07-01 03:00:00 │ 829261 │ │ 2015-07-01 04:00:00 │ 749365 │ └─────────────────────┴────────┘ ``` The [`toStartOfHour()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfHour) function used here converts the given time to the start of the hour. You can also group by year, quarter, month, or day. ## Custom grouping intervals[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-custom-grouping-intervals "Direct link to Custom grouping intervals") We can even group by arbitrary intervals, e.g., 5 minutes using the [`toStartOfInterval()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfInterval) function. Let's say we want to group by 4-hour intervals. We can specify the grouping interval using the [`INTERVAL`](https://clickhouse.com/docs/sql-reference/data-types/special-data-types/interval) clause: ``` SELECT toStartOfInterval(time, INTERVAL 4 HOUR) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; ``` Or we can use the [`toIntervalHour()`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#toIntervalHour) function ``` SELECT toStartOfInterval(time, toIntervalHour(4)) AS interval, sum(hits) AS hits FROM wikistat WHERE date(time) = '2015-07-01' GROUP BY ALL ORDER BY interval ASC LIMIT 6; ``` Either way, we get the following results: ``` ┌────────────interval─┬────hits─┐ │ 2015-07-01 00:00:00 │ 3117085 │ │ 2015-07-01 04:00:00 │ 2928396 │ │ 2015-07-01 08:00:00 │ 2679775 │ │ 2015-07-01 12:00:00 │ 2461324 │ │ 2015-07-01 16:00:00 │ 2823199 │ │ 2015-07-01 20:00:00 │ 2984758 │ └─────────────────────┴─────────┘ ``` ## Filling empty groups[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-filling-empty-groups "Direct link to Filling empty groups") In a lot of cases we deal with sparse data with some absent intervals. This results in empty buckets. Let's take the following example where we group data by 1-hour intervals. This will output the following stats with some hours missing values: ``` SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC; ``` ``` ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ <- missing values │ 2015-07-01 02:00:00 │ 1 │ <- missing values │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ <- missing values │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ ``` ClickHouse provides the [`WITH FILL`](https://clickhouse.com/docs/guides/developer/time-series-filling-gaps#with-fill) modifier to address this. This will fill out all the empty hours with zeros, so we can better understand the distribution over time: ``` SELECT toStartOfHour(time) AS hour, sum(hits) FROM wikistat WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01') GROUP BY ALL ORDER BY hour ASC WITH FILL STEP toIntervalHour(1); ``` ``` ┌────────────────hour─┬─sum(hits)─┐ │ 2015-07-01 00:00:00 │ 3 │ │ 2015-07-01 01:00:00 │ 0 │ <- new value │ 2015-07-01 02:00:00 │ 1 │ │ 2015-07-01 03:00:00 │ 0 │ <- new value │ 2015-07-01 04:00:00 │ 1 │ │ 2015-07-01 05:00:00 │ 2 │ │ 2015-07-01 06:00:00 │ 1 │ │ 2015-07-01 07:00:00 │ 1 │ │ 2015-07-01 08:00:00 │ 3 │ │ 2015-07-01 09:00:00 │ 2 │ │ 2015-07-01 10:00:00 │ 0 │ <- new value │ 2015-07-01 11:00:00 │ 0 │ <- new value │ 2015-07-01 12:00:00 │ 2 │ │ 2015-07-01 13:00:00 │ 4 │ │ 2015-07-01 14:00:00 │ 2 │ │ 2015-07-01 15:00:00 │ 2 │ │ 2015-07-01 16:00:00 │ 2 │ │ 2015-07-01 17:00:00 │ 1 │ │ 2015-07-01 18:00:00 │ 5 │ │ 2015-07-01 19:00:00 │ 5 │ │ 2015-07-01 20:00:00 │ 4 │ │ 2015-07-01 21:00:00 │ 4 │ │ 2015-07-01 22:00:00 │ 2 │ │ 2015-07-01 23:00:00 │ 2 │ └─────────────────────┴───────────┘ ``` ## Rolling time windows[](https://clickhouse.com/docs/use-cases/time-series/basic-operations#time-series-rolling-time-windows "Direct link to Rolling time windows") Sometimes, we don't want to deal with the start of intervals (like the start of a day or an hour) but window intervals. Let's say we want to understand the total hits for a window, not based on days but on a 24-hour period offset from 6 pm. We can use the [`date_diff()`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#timeDiff) function to calculate the difference between a reference time and each record's time. In this case, the `day` column will represent the difference in days (e.g., 1 day ago, 2 days ago, etc.): ``` SELECT dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day, sum(hits), FROM wikistat GROUP BY ALL ORDER BY day ASC LIMIT 5; ``` ``` ┌─day─┬─sum(hits)─┐ │ 0 │ 25524369 │ │ 1 │ 25608105 │ │ 2 │ 28567101 │ │ 3 │ 29229944 │ │ 4 │ 29383573 │ └─────┴───────────┘ ```
ML Classification
ML Categories	null
ML Page Types	null
ML Intent Types	null
Content Metadata
Language	en
Author	null
Publish Time	not set
Original Publish Time	2025-04-26 20:09:09 (12 months ago)
Republished	No
Word Count (Total)	1,431
Word Count (Content)	994
Links
External Links	5
Internal Links	92
Technical SEO
Meta Nofollow	No
Meta Noarchive	No
JS Rendered	No
Redirect Target	null
Performance
Download Time (ms)	204
TTFB (ms)	203
Download Size (bytes)	13,398
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/use-cases/time-series/basic-operations s443