Docs > Derived Time Series

Derived time series

Sometimes it’s useful to compute time series based on the data in other time series. For example, let’s say you have a series that measures temperature in degrees Celsius. Let’s call it A. You can create a derived time series that shows the same values converted to Fahrenheit, by using this formula:

A * 1.8 + 32

The resulting derived time series can be graphed and exported just like any other time series.

The data points in a derived time series are computed on demand. This means that any changes to the data of the source series are instantly reflected in the derived series. It also means that data points in derived time series do not count towards your storage limit.

Introduction to 28times formulas

28times formulas are designed for making transformations to time series. In the formula A * 1.8 + 32, the symbol A stands for the source time series, consisting of temperature values paired with timestamps. Two transformations are applied to this source time series:

  1. A * 1.8 results in a new ephemeral series with the same timestamps as A, but with each value multiplied by 1.8.
  2. Addition is applied to the ephemeral series, resulting in a new time series, where each value is now in Fahrenheit.

Several other transformations are possible, here are some examples:

  • A - B: the values from series B are subtracted from the values in series A. The result is a new time series that holds the difference between A and B at each instant.
  • sqrt(A): computes the square root of each value in A.
  • from_earlier(A, 30 min): Results in a new time series with a new set of timestamps. Each instant in the resulting series receives the value from 30 minutes earlier in the source series. Their values are unchanged, but the timestamps are effectively shifted by 30 minutes towards the future. For example, in from_earlier(A, 30 min), the value at the time 9:30 on a given date is the value at 9:00 in A. This function is useful for computing the change in value during a given time frame: The derived series A - from_earlier(A, 1 h) shows how much the metric A changed during the last hour at any given point in time.

Source time series are referenced by a single uppercase letter: A, B, C, and so on.

Operators

Five binary operators can be used in 28times formulas:

  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)
  • ^ (exponentiation)

* and / bind tighter than + and -, and ^ binds tightest. For example, A + B * C is equal to A + (B * C).

When a binary operator has a number on one side and a series on the other side, the result is a series with the same timestamps as the input series, but with the values transformed.

If a binary operator has series on both sides, the result is also a series. For example, to compute A - B, each data point from A is paired with a data point from B with the same timestamp. Any data points without a corresponding timestamp in the other series remain unused. Each value from time series B is then subtracted from the corresponding value in time series A. The result is a new time series with all timestamps that A and B have in common.

Unary - and + are also supported. They can be applied to numbers, as in -sqrt(2), or to series, as in -(A + 3).

Functions

The following functions can be applied to numbers or series:

  • sqrt(…): The square root.
  • log10(…): Base-10 logarithm.
  • ln(…): Natural logarithm.

The following functions take a series as input and return a new series.

previous(…)

Each instant in the resulting series receives the value from the next earlier data point in the source series. For example, A - previous(A) computes the difference between each value and the one that comes before it. previous(A) does not have a data point at the earliest time stamp of A.

next(…)

Each instant in the resulting series receives the value from the next later data point in the source series. next(A) does not have a data point at the latest time stamp of A.

from_earlier(…, <duration>) and from_later(…, <duration>)

Look up values at an earlier/later instant. The first argument must be a time series. The result is a time series with a new set of timestamps.

<duration> is a combination of a number and a unit, for example 30 min or 1 h. The number must be an integer. Valid units are: s (seconds), min (minutes), h (hours), d (days), w (weeks), mo (months), y (years).

Note that these functions are named from the perspective of the resulting time series: in the result of from_earlier, timestamps get the value that is associated with an earlier timestamp in the input series. In the result of from_later, timestamps get the value that is associated with a later timestamp in the input series.

For example, a time series with value 1 at 8:00 and 2 at 9:00 on some day would be the result of a from_earlier(A, 1 h) operation where A has value 1 at 7:00 and 2 at 8:00.

For a duration measured in seconds, the resulting time series has timestamps which are set apart from the input by exactly that number of seconds. A duration 1 min is exactly the same as 60 s, and 1 h is exactly the same as 60 min or 3600 s. Time zones don’t affect the calculations for seconds, minutes, and hours, because the calculations are done in terms of the actual number of seconds passed. This means that jumps due to daylight saving time are correctly taken into account: Two hours after 1:00 AM is usually 3:00 AM. But in some places, on rare occasions, due to daylight saving time, two hours after 1:00 AM may be 2:00 AM or 4:00 AM.

When the time unit is days or longer, the resulting time series takes on the value from the same time of day, but on a different date. For example, the timestamp 2020-01-04 05:00 is one day after 2020-01-03 05:00. In most cases, this results in a difference of exactly 24 hours, but on rare occasions, it does not. For example, daylight saving time results in one day each year having 23 hours, and another having 25 hours.

If the time zone of the derived series is UTC, a duration of one day is exactly the same as 24 hours.

A duration of one week is exactly the same as 7 days.

A duration of one or more months works similarly to a duration measured in days: the day and time stay the same, but the month changes. The year may change too, of course. For example, 3 months after 1999-12-31 00:00 is 2000-03-31 00:00.

A duration of one year is exactly the same as 12 months.

If – for a duration measured in days, weeks, months, or years – the targeted timestamp doesn't exist, the last timestamp before the targeted timestamp is used. For example, moving 2020-01-30 15:00 forward by one month results in 2020-02-29 23:59:59, because there is no 30th of February.

If you have data spread throughout the month, consider using a duration of 30 days instead of one month. This avoids discontinuities when the months don't have the same number of days. On the other hand, if data points are always associated with the first day of a month, a duration of one month is probably best.

bucket(…, <period>, <aggregation>)

The bucket() function is an experimental feature that may be changed or replaced by a different function in the future.

The bucket function aggregates values from a time period. The first argument must be a time series.

Possible periods are:

  • hourly
  • daily
  • weekly_mon
  • weekly_tue
  • weekly_wed
  • weekly_thu
  • weekly_fri
  • weekly_sat
  • weekly_sun
  • monthly
  • yearly

With bucket, all data points falling within a time period are aggregated and the resulting value is associated with the timestamp at the start of the period. Hourly periods result in timestamps with minutes and seconds set to 0. For example, in a time series with data points at 2020-03-14 08:15 and 2020-03-14 08:45, the resulting time series with period hourly will aggregate the two values to a single data point at 2020-03-14 08:00. With period daily, the resulting data point is at 2020-03-14, which is equal to 2020-03-14 00:00.

Weekly periods result in timestamps at the start of the day of the week in the period name. For example weekly_mon aggregates values from Monday 00:00:00 to Sunday 23:59:59.

Monthly periods start on the first day of each month at midnight. Yearly periods start on January 1st at midnight.

Possible aggregations are:

  • mean: arithmetic mean of all values
  • median: the median of all values
  • max: maximum, the highest value
  • min: minimum, the lowest value
  • count: number of values
  • first: the earliest value
  • last: the latest value
  • sum: the sum total

Constants

  • pi: the circle ratio π, 3.14159…
  • e: Euler’s constant, 2.71828…

Details

Numeric values

All numeric values are IEEE-754 64-bit floating point numbers, also known as doubles. Values resulting from a calculation can be Infinity or NaN, for instance, as the result of a division by zero.

Number literals are decimal numbers. The following rules apply:

  • A leading zero before a decimal point is optional: 0.5 and .5 are equivalent.
  • A decimal point and following zeros are optional: 1, 1., and 1.0 are equivalent.
  • Leading zeros are allowed: 021 is equivalent to 21.
  • Scientific notation is allowed: 3e5 and 3.0e+5 are equivalent to 300000.
  • A literal that would overflow to Infinity is an error.

Time zones

Mixing input series with different time zones in a formula is possible. Each data point has an instant that is independent of the time zone. The same instant has different textual timestamp representations in different time zones. For example, 2020-01-30 20:00 in America/New_York is equal to 2020-01-31 10:00 in Asia/Tokyo. Conversely, the same textual timestamp representation may refer to different points in time in different time zones.

If a derived time series has a formula using from_earlier(), from_later(), or bucket(), the time calculations use the time zone of that derived time series.

Order of operations

In the absence of parentheses, the operator precedence is as follows, with the highest precedence at the top.

  • (…) (parenthesized expressions) and <function>(…) (function calls such as sqrt(…)).
  • ^ (exponentiation) and unary operators - and +.
  • Binary operators * and /.
  • Binary operators + and -.

All binary operators except ^ are left-associative as usual: A - B - C is equal to (A - B) - C. ^ is non-associative, meaning that a chain of ^ operators, such as 3^4^5 is invalid and needs mandatory parentheses to clarify the order of operations. A formula like -3^2 is also invalid and needs clarifying parentheses: (-3)^2 or -(3^2).

Parsing rules

Adjacent operators must be separated by a space. For example, A ^-2 is not allowed, but A ^ -2 is. Adjacent unary operators are forbidden to avoid mistakes: (- - A) is not allowed because it’s probably a typo. It can be written as (-(-A)) or simply (A).