# interval

**Syntax**

interval(X, duration, fill, [step], [explicitOffset=false], [closed], [label], [origin])

**Details**

In SQL queries, group data into continuous intervals with the length of *duration*. For intervals without any data, fill the results using the interpolation method specified by *fill*. This function must be used in a SQL *group by* clause.

Note: Temporal data types in “where” conditions are automatically converted.

**Arguments**

X is a vector of integral or temporal type.

duration is of integral or duration type. The following time units are supported (case-sensitive): w, d, H, m, s, ms, us, ns. As time units *M* and *y* are not supported, to group *X* by year or month, convert the data format of *X* with function month or year.

fill indicates how to fill the missing values of the result. It can take the value of “prev”, “post”, “linear”, “null”, a specific numeric value and “none”.

“prev”: the previous value

“post”: the next value

“linear”: linear interpolation. For non-numerical data, linear interpolation cannot be used and the “prev” method will be used instead.

“null”: null value

a specific numeric value.

“none”: do not interpolate

step is of integral or duration type. It’s an optional parameter indicating the step size of the sliding window. It must be a number which can divide the duration. This parameter allows us to specify a sliding interval that is smaller than *duration*. The default value is the same as *duration*, which means the calculation window slides by the length of *duration*.

Note: If step is specified, the following aggregation calculations are not supported: atImax, atImin, difference, imax, imin, lastNot, mode, percentile.

explicitOffset is an optional BOOLEAN. When *explicitOffset* = true, the first interpolation window starts at the starting point specified by the SQL *where* clause. When *explicitOffset* = false (default), the first interpolation window starts at the nearest point that is divisible by step before the starting point specified by the SQL *where* clause.

closed is a string indicating which boundary of the interval is closed. It can be specified as ‘left’ or ‘right’.

label is a string indicating which boundary is used to label the interval with. It can be specified as ‘left’ or ‘right’.

origin is a string or a scalar of the same temporal type as *X*, indicating the timestamp when the intervals start. When origin is a string, it can be specified as:

‘epoch’: 1970-01-01;

‘start’: the first value of the timeseries;

‘start_day’: 00:00 of the first day of the timeseries;

‘end’: the last value of the timeseries;

‘end_day’: 24:00 of the last day of the timeseries.

Note that *origin* is specified only when *explicitOffset* = false.

Note: As of version 1.30.14, the *range* parameter has been removed.

The following figure uses duration = 2 * step as an example to further explain how the interpolation window slides and applies calculations. The windows whose results are NULL are filled using the method specified by *fill*.

The figure below explains how the starting interpolation window is determined based on *explicitOffset*. In this example, t1 is the starting point specified by the where condition, t0 is the nearest point before t1 that is divisible by *step*. When *explicitOffset* = true, the first interpolation window starts at t1. When *explicitOffset* = false, the first interpolation window starts at t0.

**Examples**

Fill with the previous value:

```
$ timestampv = temporalAdd(2012.01.01T00:00:00.000, 0..11 join 15..20 , "s")
$ a1v = [3,2.5,1.7,1.1,1.8,2.1,1.1,1.4,1.9,2.4,2.9,2.6,1.1,2.7,1.1,2.9,1.9,1.7]
$ t = table(timestampv as timestamp, a1v as a1)
$ select max(a1) from t group by interval(timestamp, 3s, "prev")
```

interval_timestamp |
max_a1 |
---|---|

2012.01.01T00:00:00.000 |
3 |

2012.01.01T00:00:03.000 |
2.1 |

2012.01.01T00:00:06.000 |
1.9 |

2012.01.01T00:00:09.000 |
2.9 |

2012.01.01T00:00:12.000 |
2.9 |

2012.01.01T00:00:15.000 |
2.7 |

2012.01.01T00:00:18.000 |
2.9 |

fill with a specific number:

```
$ select max(a1) from t group by interval(timestamp, 3s, 100)
```

interval_timestamp |
max_a1 |
---|---|

2012.01.01T00:00:00.000 |
3 |

2012.01.01T00:00:03.000 |
2.1 |

2012.01.01T00:00:06.000 |
1.9 |

2012.01.01T00:00:09.000 |
2.9 |

2012.01.01T00:00:12.000 |
100 |

2012.01.01T00:00:15.000 |
2.7 |

2012.01.01T00:00:18.000 |
2.9 |

In the following example, to group data by every 2 years, convert *X* with the `year`

function, and specify *duration* as an integer in `interval`

.

```
$ t=table([2016.10.12T00:00:00.500,2017.10.12T00:00:03.000,2018.10.12T00:00:03.000,2019.10.12T00:00:08.000,2020.10.12T00:00:08.000,2021.10.12T00:00:08.000] as time, [7,9,NULL,NULL,8,6] as price)
$ select max(price) from t group by interval(X=year(time), duration=2, fill="prev")
```

interval |
max_price |
---|---|

2016 |
9 |

2018 |
9 |

2020 |
8 |

The following example demonstrates how the starting point of the first window is determined based on the value of *explicitOffset*.

```
$ symbol = `A`A`A`A`A`A`A`A`B`B`B`B
$ price= [29.55,29.74,29.51,29.54,29.79,29.81,29.50,29.56,29.41,29.49,29.83,29.76]
$ volume = [2200,1900,2100,3200,8800,5800,4300,9300,7900,9100,7300,6500]
$ tradeTime = [09:33:56,09:33:59,09:34:08,09:34:16,09:34:51,09:34:59,09:35:47,09:35:26,09:35:36,09:36:26,09:37:12,10:00:00]
$ t = table(tradeTime, symbol, volume, price)
//When explicitOffset is set to true, the first interpolation window starts at 09:33:50 specified by where clause.
$ select max(price) as max_price, min(price) as min_price from t where tradeTime between 09:33:50:09:35:00 group by symbol, interval(X=tradeTime, duration=30, fill="post", explicitOffset=true) as tradeTime
```

symbol |
tradeTime |
max_price |
min_price |
---|---|---|---|

A |
09:33:50 |
29.74 |
29.51 |

A |
09:34:20 |
29.81 |
29.79 |

A |
09:34:50 |
29.81 |
29.79 |

When *explicitOffset* is set to false, the starting point of the first window should be the first value before 09:33:50 (the starting point of the time range specified by the *where* condition) that is divisible by *step*. Therefore, the starting point of the first window should be second(09:33:50/30*30), which is 09:33:30.

```
$ select max(price) as max_price, min(price) as min_price from t where tradeTime between 09:33:50:09:35:00 group by symbol, interval(X=tradeTime, duration=30,fill="prev",explicitOffset=false) as tradeTime
```

symbol |
tradeTime |
max_price |
min_price |
---|---|---|---|

A |
09:33:30 |
29.74 |
29.55 |

A |
09:34:00 |
29.54 |
29.51 |

A |
09:34:30 |
29.81 |
29.79 |

A |
09:35:00 |
29.81 |
29.79 |

The following example sets *step* to 20s and *duration* to 60s. The calculation is performed by sliding forward every 20s.

```
$ select max(price) as max_price, min(price) as min_price from t where tradeTime between 09:33:50:09:35:00 group by symbol, interval(X=tradeTime, duration=60, fill=0, step=20, explicitOffset=false) as tradeTime
```

symbol |
tradeTime |
max_price |
min_price |
---|---|---|---|

A |
09:33:40 |
29.74 |
29.51 |

A |
09:34:00 |
29.81 |
29.51 |

A |
09:34:20 |
29.81 |
29.79 |

A |
09:34:40 |
29.81 |
29.79 |

A |
09:35:00 |
0.00 |
0.00 |