Skip to content
Login
Try for free
Login
Try for free
Solutions

Time Series Database
that truly Scales

Don’t worry about performance issues with your time-series queries any more. With CrateDB, your data volume doesn’t bring your performance down, that’s why it is so unique. You keep getting query results in milliseconds, even for complex queries, multiple data types and high ingest rates, while storing years of data.
 

Instant time-series queries, all with SQL

 

        
        

/* Based on device data, this query returns the average
 * of the battery level for every hour for each device_id
 */
WITH avg_metrics AS (
    SELECT device_id,
       DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
       AVG(battery_level) AS avg_battery_level
    FROM devices.readings
    GROUP BY 1, 2 
    ORDER BY 1, 2
)
SELECT period,
       t.device_id,
       manufacturer,
       avg_battery_level  
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id 
      AND model = 'mustang'
LIMIT 10;


        
        
        
        

+---------------+------------+--------------+-------------------+
|    period     |  device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 |    iobeam    | 49.25757575757576 |
| 1480806000000 | demo000001 |    iobeam    | 47.375            |
| 1480802400000 | demo000007 |    iobeam    | 25.53030303030303 |
| 1480806000000 | demo000007 |    iobeam    | 58.5              |
| 1480802400000 | demo000010 |    iobeam    | 34.90909090909091 |
| 1480806000000 | demo000010 |    iobeam    | 32.4              |
| 1480802400000 | demo000016 |    iobeam    | 36.06060606060606 |
| 1480806000000 | demo000016 |    iobeam    | 35.45             |
| 1480802400000 | demo000025 |    iobeam    | 12                |
| 1480806000000 | demo000025 |    iobeam    | 16.475            |
+---------------+------------+--------------+-------------------+


        
        
        
        

/* To identify gaps on the readings, the following queries generates a series
 * and by joining it with the original data, you can spot any gap */
with avg_battery AS (
  SELECT battery_level, time 
  FROM devices.readings
  WHERE device_id = 'demo000007' 
  AND time > 1480118400000 
  AND time < 1480301200000
  ORDER BY 2
),

all_hours AS (
  SELECT generate_series(1480118430000,1480301200000,'30 second'::interval) AS generated_hours
)

SELECT time, generated_hours, battery_level
FROM all_hours 
LEFT JOIN avg_battery ON generated_hours = time
ORDER BY 2
LIMIT 20;


        
        
        
        

+---------------+---------------+---------------+
|          time |         hours | battery_level |
+---------------+---------------+---------------+
| 1480118430000 | 1480118430000 |            67 |
| 1480118460000 | 1480118460000 |            66 |
| 1480118490000 | 1480118490000 |            66 |
| 1480118520000 | 1480118520000 |            66 |
| 1480118550000 | 1480118550000 |            66 |
| 1480118580000 | 1480118580000 |            66 |
| 1480118610000 | 1480118610000 |            65 |
| 1480118640000 | 1480118640000 |          NULL |
| 1480118670000 | 1480118670000 |            65 |
| 1480118700000 | 1480118700000 |            65 |
| 1480118730000 | 1480118730000 |            65 |
| 1480118760000 | 1480118760000 |            65 |
| 1480118790000 | 1480118790000 |            65 |
| 1480118820000 | 1480118820000 |            65 |
| 1480118850000 | 1480118850000 |            65 |
| 1480118880000 | 1480118880000 |            65 |
| 1480118910000 | 1480118910000 |            65 |
| 1480118940000 | 1480118940000 |            65 |
| 1480118970000 | 1480118970000 |          NULL |
| 1480119000000 | 1480119000000 |          NULL |
+---------------+---------------+---------------+


        
        
        
        

/* Based on device data, this query returns the number of battery charges 
 * per day for a given device_id */
WITH aux_charging AS (
    SELECT time, 
      DATE_BIN('P1D'::INTERVAL,time,0) AS day, 
      battery_status, 
      LAG(battery_status) OVER (PARTITION BY device_id ORDER BY time) AS prev_battery_status
    FROM devices.readings
    WHERE device_id = 'demo000001'
    ORDER BY time
  ),

count_start_charging AS (
    SELECT day, (case when battery_status <> prev_battery_status then 1 else 0 end) AS start_charging
    FROM aux_charging
    ORDER BY 1
  )

SELECT day, sum(start_charging) as charges_number
FROM count_start_charging
GROUP BY 1
ORDER BY 1;


        
        
        
        

+---------------+---------------+
| count_charges |           day |
+---------------+---------------+
|             2 | 1479168000000 |
|             4 | 1479254400000 |
|             2 | 1479340800000 |
|            10 | 1479427200000 |
|             7 | 1479600000000 |
|             8 | 1479686400000 |
|             6 | 1479772800000 |
|            11 | 1479859200000 |
|             5 | 1480032000000 |
|             7 | 1480118400000 |
|             6 | 1480204800000 |
|            10 | 1480291200000 |
|             3 | 1480464000000 |
|             3 | 1480550400000 |
|             7 | 1480636800000 |
|             2 | 1480723200000 |
+---------------+---------------+


        
        
        
        

/* Based on device data, this query returns the average of the battery temperature
 * for each OS version */
SELECT device_info['os_name'], avg(battery_temperature)
FROM "devices"."readings"
GROUP BY 1
LIMIT 100;

        
        
        
        
 
+---------+--------------------------+
| os_name | avg(battery_temperature) |
+---------+--------------------------+
| 4.4.4   |        90.85937893039049 |
| 5.1.0   |        90.86754559738132 |
| 6.0.1   |        90.84230101265824 |
| 5.0.0   |        90.8574802739726  |
+---------+--------------------------+

Time-series data and analysis can help businesses understand patterns, trends, and causes over time. This is crucial for business success.

CrateDB is the perfect time-series database. It is built to ingest and manage massive amounts of data from diverse sources. As a Native SQL DBMS, CrateDB is easy to learn, easy to integrate, and does not lock you into proprietary data access interfaces as so many time-series databases do.

Distributed architecture

CrateDB is a distributed database that can scale horizontally across multiple nodes. This makes it an ideal fit for time series workloads, which often involve large volumes of data that need to be ingested and processed in real-time.

Columnar storage

CrateDB uses a columnar storage format, which is highly efficient for time series data. Columnar storage allows for faster query performance, especially when aggregating data over time periods or subsets of the data.

SQL interface

CrateDB supports SQL, which is a familiar and powerful query language for many developers. This makes it easy to query and analyze time series data without needing to learn a new query language or tool. CrateDB also provides a HTTP endpoint to submit SQL queries.

Time series functionality

CrateDB includes built-in time series functionality, such as window functions and time-based indexes. These features make it easier to query and analyze time series data, and can improve query performance.

Open source

CrateDB open source licensing model leverages the power of an active community and brings your licensing costs down. Whether you want complete peace of mind with the SaaS model or deploy the product yourself, we have the right option for you.
Learn more about the product

Interested?

Get started for free
Schedule a meeting
Time-series-data-From-raw-data-to-fast-analyis-in-only-three-steps

Live Demo: Time-series data – From raw data to fast analysis in only three steps

Learn about all the data modeling decisions needed for implementing time-series data in CrateDB, including sharding/ partitioning best practices. Get a demo on the CrateDB time-series query functions to get realtime insights.

Watch Now

Other time-series resources

White Paper

How to get the most out of your Time Series Data

In this white paper, we will explore how you can get the most out of your time series data showing you some real-life example.

White Paper

Time-series data in manufacturing

Interested in industrial time-series? This white paper answers all your questions, diving deep into the topic.

White Paper

Benchmark: CrateDB vs InfluxDB CrateDB processes industrial time series up to 10x faster.

TUTORIAL

Sharding and partitioning guide for time-series data

March 28, 2023
The goal of this guide is to support you with building a sharding and partitioning strategy for your time series data.

TUTORIAL

Optimizing storage for historic time-series data

March 28, 2023
When dealing with time-series data, performance is crucial. Data that gets ingested should be available quickly for querying. As data ages, it is typically involved less often in real-time feedback loops, making query performance less of a concern. Instead, storage cost becomes more important as data accumulates.

Tutorial

Interpolating missing time-series values

March 29, 2023
When dealing with time-series data, you will likely at some point run into the question of how to deal with incomplete data. This could be caused by a sensor outage, network issues, or any other factors resulting in missing data. Two common interpolation techniques are Last Observation Carried Forward (LOCF) and Next Observation Carried Backward (NOCB), which we will discuss in this article.

TUTORIAL

Resampling time-series data with DATE_BIN

March 24, 2023
CrateDB 4.7 adds a new DATE_BIN 6 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.

Want to know more?

Get started for free
Schedule a meeting

101 for Time-Series databases

  • What is a time series database?
  • Key criteria for selecting a time series database.

What our customers say

"CrateDB is a highly scalable database for time series and event data with a very fast query engine using standard SQL."
cr-quote-img-white
Alexander Mann Digital Core TGW Logistics
"Thanks to CrateDB's great indexing, dedicated data types, and subsequent great performance, we could execute an event and data-driven architecture, with the performance and scalability necessary for storing time-series data over time."
cr-quote-img-white
Kristoffer Axelsson Principal Solution Architect Thomas Concrete Group
"We use CrateDB to process a lot of system metrics data (time-series) in real time and to support streaming log analytics, which required search capability. I'm glad we can make queries with SQL. If we had to use Elasticsearch to answer new questions, we wouldn't be nearly as responsive to new requirements."
cr-quote-img-white
Joe Hacobian Infrastructure Engineer Digital Domain

Product documentation

How to generate time series data

This collection of tutorials will show you how to generate mock time series data about the International Space Station (ISS) and write it to CrateDB using the client of your choice.
 

How to normalize time series data intervals

This tutorial demonstrates the shortcomings of visualizing the non-normalized data and shows you how to address thesem by normalizing your data using SQL.
 

Date and Time functions

Explore the list of all date and time functions you can use when querying CrateDB data.
 

Blog

Visualizing time series data with Grafana and CrateDB

Grafana is an open-source tool that helps you build real-time dashboards, graphs, and all sorts of data visualizations. It is the perfect complement to CrateDB, which is purpose-built for monitoring large volumes of machine data in real-time.

Read more
cr-grafana

Introduction to Time-Series Visualization in CrateDB and Superset

In this tutorial post, we'll showcase how to build the following Superset dashboard powered by data in CrateDB.

Read more
cr-supersetx2

101 for Time-Series databases

What is Time-Series Data? Understanding the basics

 

Challenges when Analyzing Time-Series Data