Working with Time Functions

Time-series data are measurements or events that are monitored, where time is a critical dimension to the data. This could be server metrics, application performance monitoring metrics, network data, logs, sensor data, events, clicks, IOT, and many other types of analytics data.

Time-series analytics is the measurement of change over time.

All fields in Hydrolix are indexed, however, time is the primary index. Time as the primary key is one of the reasons why Hydrolix can get query results from object storage quickly. Internally, a table consists of many time partitioned data files in a columnar format. Each file represents sequentially time ordered data and associated metadata. This is all handled behind the scenes and invisible to the user or administrator.

Working with time

Since Hydrolix uses time as a primary key, queries that reference tables must contain a time range in the WHERE clause. This is essential for time partition lookup.

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 usingquantiles(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!