Axibase Time Series Database

Getting Started: SQL

Contents

  1. Introduction
  2. Inserting Data
  3. Portals
  4. Exporting Data
  5. SQL
  6. Alerting

SQL Console

ATSD supports SQL syntax for flexible querying and analysis of time series data. One of the options available for executing ad hoc SQL queries is the built-in SQL Console.

Open SQL > SQL Console from the main menu:

Retrieve the five most recent values for the temperature series.

SELECT entity, metric, datetime, value
  FROM "temperature"
WHERE entity = 'br-1905'
  AND datetime >= '2019-03-01T00:00:00Z'
ORDER BY datetime DESC
  LIMIT 5

Export query results into CSV, Excel, and JSON by clicking Export.

Modify the query to include metadata fields, tags, and to format the date:

SELECT entity.label AS "asset", entity.tags.model AS "model",
  metric, metric.units AS "units",
  date_format(time, 'yyyy-MM-dd HH:mm:ss', 'US/Pacific') AS "date", value
  FROM "temperature"
WHERE entity = 'br-1905'
  AND datetime >= '2019-03-01T00:00:00Z'
ORDER BY datetime DESC
  LIMIT 5

| asset    | model   | metric       | units    | date                 | value |
|----------|---------|--------------|----------|----------------------|-------|
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:53  | 25.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:47  | 32.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:15  | 20.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:08  | 31.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:02  | 31.0  |

Regularize a series with a step-like interpolation function:

SELECT entity.label AS "asset", entity.tags.model AS "model",
  metric, metric.units AS "units",
  date_format(time, 'yyyy-MM-dd HH:mm:ss', 'US/Pacific') AS "date", value
  FROM "temperature"
WHERE entity = 'br-1905'
  AND datetime >= '2019-03-01T00:00:00Z'
WITH INTERPOLATE(15 SECOND, PREVIOUS)
ORDER BY datetime DESC
  LIMIT 5

| asset    | model   | metric       | units    | date                 | value |
|----------|---------|--------------|----------|----------------------|-------|
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:49:00  | 25.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:45  | 20.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:30  | 20.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:15  | 20.0  |
| BR-1905  | 4520-1  | temperature  | Celsius  | 2019-03-04 02:48:00  | 30.0  |

Review the SQL syntax and experiment by executing custom queries based on the provided examples.

Continue to Part 6: Alerting.

← 6. Alerting →