# Resampling time-series data with DATE\_BIN

**URL:** <https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009>\
**Category:** Tutorials\
**Tags:** sql, fundamentals\
**Created:** [February 11, 2022, 8:35am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009 "2022-02-11T08:35:51Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [February 11, 2022, 8:35am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/1 "2022-02-11T08:35:51Z")

</div>

## Introduction

CrateDB 4.7 adds a new [DATE\_BIN](https://crate.io/docs/crate/reference/en/master/general/builtins/scalar-functions.html#date-bin-interval-timestamp-origin) function, offering greater flexibility for grouping rows into time buckets. This article will show how to use `DATE_BIN` to group rows into time buckets and resample the values.

In previous versions, [DATE\_TRUNC](https://crate.io/docs/crate/reference/en/master/general/builtins/scalar-functions.html#date-trunc-interval-timezone-timestamp) could be used for similar requirements but had its limitations. With `DATE_TRUNC`, timestamps can be truncated on different intervals, such as seconds, minutes, hours, and so on. But it is not possible to create time buckets based on more complex intervals, such as 10 seconds, 5 minutes, or similar intervals.

## Data model

We assume a simple time-series table as a basis:

```sql
CREATE TABLE sensor_readings (
   time TIMESTAMP WITH TIME ZONE NOT NULL,
   sensor_id INTEGER NOT NULL,
   battery_level DOUBLE PRECISION,
   battery_status TEXT,
   battery_temperature DOUBLE PRECISION
);

```

The table is filled with a number of rows. The rows follow no clear sampling rate (i.e. are not consistently appearing every minute).

 ![date_bin_1](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/184e5dc833c2dca3d6c946374d0836e2bf57dc1e.png)

## Time-bucketing and resampling

As measurements in our table occur in irregular intervals, we now want to resample them in time buckets of five minutes. For each time bucket, one row should be returned. Different approaches are possible to achieve this downsampling. We will use the latest row in each time bucket as the representative, but other approaches (like calculating the average) are also possible.

This leads to the following query, which:

1. Uses `DATE_BIN` to generate time buckets of 5 minutes each
2. Assigns every row within a time bucket a `ROW_NUMBER`, ordered by timestamp descending. This means, that the latest row will be assigned the value `1`.
3. The query is wrapped in a subquery so that the `row_number` column can be referenced and filtered in the `WHERE` clause

```sql
SELECT ts_bin,
       battery_level,
       battery_status,
       battery_temperature
FROM (
  SELECT DATE_BIN('5 minutes'::INTERVAL, "time", 0) AS ts_bin,
         battery_level,
         battery_status,
         battery_temperature,
         ROW_NUMBER() OVER (PARTITION BY DATE_BIN('5 minutes'::INTERVAL, "time", 0) ORDER BY "time" DESC) AS "row_number"
  FROM doc.sensor_readings
) x
WHERE "row_number" = 1
ORDER BY 1 ASC

```

The output shows that three time buckets have been created based on the table’s rows:

 ![date_bin_2](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/5c85d860ca1eefb581d0231ea8dffd074ce74754.png)

## Conclusion

With the addition of `DATE_BIN` in CrateDB 4.7, highly flexible intervals can now be used to define time buckets. `DATE_TRUNC` can still be used as an alternative for simple time bucketing requirements, if sufficient.

Want to learn more about working with time series data in CrateDB? Our Advanced Time Series course is a free, online course that will teach you all you need to know. Earn a certificate of completion to share on your LinkedIn profile! Enroll at [learn.cratedb.com](https://learn.cratedb.com).

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [October 2, 2024, 3:10pm UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/2 "2024-10-02T15:10:36Z")

</div>

@hammerhead, thank you for bringing up this topic. 🤩

Let’s assume 🤪 I have another data model approach which is as follows:

 ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/6/621c7e4fca6d8c81ea7ca28e096f383ce2b50e38.png)  
I would like to perform fast aggregations and resampling on each of the sensors. I came up with this design because the sensors in my case are very dynamic, and I didn’t want to fill up my database with thousands of columns that contain no data 😵‍💫. However, I am now facing some real performance bottlenecks when it comes to resampling and WHERE conditions.

How would you approach resampling on such a design? Additionally, is this design bad in general for time-series databases like CrateDB?

Regards, Schabi

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [October 4, 2024, 7:52am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/3 "2024-10-04T07:52:01Z")

</div>

Hi @SchabiDesigns,

Can you share a few details on how your table is set up in regards to sharding/partitioning, as well as data volume? The output of the following two queries would be interesting to see:

```sql
SHOW CREATE TABLE your_schema_name.your_table_name;

SELECT id, num_docs, size
FROM sys.shards
WHERE primary = TRUE
   AND schema_name = 'your_schema_name'
   AND table_name = 'your_table_name'

```

And what does the resampling query look like that shows slow performance?

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [October 14, 2024, 6:44am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/4 "2024-10-14T06:44:53Z")

</div>

Hi Niklas @hammerhead  
Sure thing… thanks for your time! 🤩

```auto
CREATE TABLE IF NOT EXISTS "my_schema"."dataset" (
   "experiment_id" BIGINT,
   "sensor_short_name" VARCHAR(20),
   "date_time" TIMESTAMP WITH TIME ZONE,
   "quantity" DOUBLE PRECISION,
   "partition_id" SMALLINT
)
CLUSTERED INTO 16 SHARDS
PARTITIONED BY ("partition_id")
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

```

the other query looks a bit weird to me… seems like there are some reminders from my previous clearing action 🙃. partition id makes sure partitions are around 160GB each.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/b/bcb67ee12828253fa4e2abff06b5e15636edd930.png)

anyway I think the main problem causes from my query I have to perform to get the data I need…

**in the easiest case**  
`SELECT * FROM my_schema.dataset WHERE experiment_id IN *{ids}*`  
 → up to 1000 ids 🙄

**in the worst case**

```auto
SELECT a.* FROM (
  SELECT experiment_id, sensor_short_name, date_time, quantity FROM my_schema.dataset 
  WHERE experiment_id IN *{ids}*
) AS a INNER JOIN (
  SELECT experiment_id, date_time FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY experiment_id, quantity) AS row_num FROM my_schema.dataset
    WHERE sensor_short_name = 'tcy' AND experiment_id IN *{ids}*                                                        
  ) AS cycles
  WHERE cycles.row_num % *{every}* = 0
) AS b
ON (a.experiment_id = b.experiment_id AND a.date_time = b.date_time);

```

In this case I take only some of the data depending on the _every_ variable. I would rather resample like you did…

→ I would like to filter by experiment\_id and then resample for all the available sensors. No idea how to get to this in crate itself… python sure thing but that means lot of data-loading in the first place and very bad user experience 🤢

Things get very nasty with this kind of database table design, thought it would be clever in this way due to very flexible data formats 😵‍💫

Regards  
Schabi

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [October 15, 2024, 7:27am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/5 "2024-10-15T07:27:52Z")

</div>

Hi @SchabiDesigns,

The partitioning strategy is probably not ideal for those types of queries. Mainly because `partition_id` is not used as a `WHERE` condition in your queries. For the two queries you shared, CrateDB needs to go through the entire table (all partitions), even if all relevant rows are in just one partition (since there is no `WHERE` condition on `partition_id`). This will make the query slower with more partitions.

Without knowing the details around your data model, do you see an option to partition by another column that you can filter by in your queries? `experiment_id` may or may not be an option, depending on how many experiments you store in the table (should not exceed 1000 partitions per table) and how data volumes differ between experiments (equal partition size).

For the approach with `ROW_NUMBER()` and downsampling using the modulo operator, you may also find the build-in [\_docid column](https://cratedb.com/docs/crate/reference/en/latest/general/ddl/system-columns.html) useful. A condition such as `WHERE _docid % 10` returns every 10th document without having to compute row numbers. However, it is slightly different compared to your approach where you generate the row numbers after filtering for a set of `experiment_id`s and with an `ORDER BY`. The `_docid` numbering is applied globally across the table/partition at the time of ingestion.

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [October 15, 2024, 9:28am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009/6 "2024-10-15T09:28:18Z")

</div>

Thanks for your support @hammerhead

Yes the partition\_id I could include in the query. Thanks for that!

I don’t see _experiment\_id_ as an option for partitioning due to unknown data size. One experiment can be only 1 value another can be 1GB of data. The number of sensors for each experiment is also variable from 1 to 50 or more 🙄  
I will try your recommendation to use the _\_docid_ approach and check if that helps a bit 👍

Maybe a general question. How would you design a table to store such variable data and still be able to query it fast?