# Timeseries Functions

```
SELECT count()
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-02 12:00:00')
```

Date Time can be specified as absolute value `'2016-01-01 00:00:00'`

, relative value `now()`

, `yesterday()`

or calculated using Date Time arithmetic add(), subtract() and interval functions

## Grouping time

Grouping and subdivision of time ranges is achieved through the use of time functions (reference). These functions can be used in either `SELECT expression`

or `GROUP BY clause`

position.

There are general time functions for `toStartOfYear()`

, `toStartOfMonth()`

, `toStartOfDay()`

, `toStartOfMinute()`

, `toStartOfSecond()`

. Along with common patterns `toStartofFiveMinute()`

, Ten and Fifteen and many more. Each function accepts a Date Time data type and rounds values to their respective time unit.

```
SELECT toStartOfFiveMinute(timestamp) AS five, max(usage_guest)
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 04:00:00')
GROUP BY five
ORDER BY five
```

This will aggregate the data into 5 minutes buckets and compute max values of the specified metric. If the time buckets are not required in the result, we could move the function into the `GROUP BY`

clause.

```
SELECT max(usage_guest)
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 04:00:00')
GROUP BY toStartOfMinute(timestamp) AS five
ORDER BY five
```

## Fine grained time grouping

Fine grained grouping can be achieved using `toStartOfInterval(t, INTERVAL unit)`

where unit can be `year|month|day|minute`

```
SELECT toStartOfInterval(timestamp, INTERVAL 3 hour) AS three, max(usage_guest)
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-02 00:00:00')
GROUP BY three
ORDER bY three
```

## Using time functions together

Time functions can be used together for more complex calculations.

```
SELECT toStartOfHour(timestamp) AS hour, count()
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 02:00:00')
GROUP BY hour, toStartOfInterval(timestamp, INTERVAL 30 minute) AS mins
ORDER BY hour, mins
```

## Equally sized time slots

`timeSlots(t, duration, size)`

is useful for defining equally sizes slots. Where duration is specified in seconds as UInt32 (type conversion) and size controls the number of slots contained in duration.

```
SELECT timeSlots(timestamp, toUInt32(60*10), 6*10) AS slots, max(usage_guest)
FROM sample.metrics
WhERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 01:00:00')
GROUP BY slots
ORDER BY slots
```

This produces 600 second slots, each containing an array of time in 60 second intervals.

## Time aggregations

Finding the latest value of a metric is a common monitoring requirement. `argMax(metric,t)`

succinctly expresses finding the latest value over a specified time range.

```
SELECT argMax(usage_guest, timestamp)
FROM sample.metrics
WHERE (timestamp between '2016-01-01 00:00:00' AND '2016-01-01 12:00:00')
```

## Rate of change over time

Calculating the rate of change over time is an important concept in time series analytics. `boundingRatio(t,metric)`

will calculate rate change based on metric argMax - argMin / max - min time. Adding a grouping time function will give you a breakdown per time bucket.

```
SELECT team, boundingRatio(timestamp, usage_iowait) AS rate
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 01:00:00')
GROUP BY team
ORDER BY rate DESC
```

## Range of quantiles

Calculating a range of quantiles for a single metric in a single pass is also easy using`quantiles(L1, L2, ..)(metric)`

. Where levels are specified as a number between 0 and 1.

```
SELECT quantiles(0.25, 0.5, 0.75, 0.99)(usage_guest)
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 12:00:00')
```

## Aggregations on groups of data

`<aggregation>Resample(start, end, step)(<aggFunction_params>, resampling_key)`

lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.

```
SELECT countResample(80,90,2)(usage_guest, usage_guest)
FROM sample.metrics
WHERE (timestamp BETWEEN '2016-01-01 00:00:00' AND '2016-01-01 12:00:00')
```

This groups the metric values [80, 82, 84, 86, 88, 90] and provides counts for each bucket. Very useful!