Skip to main content
Version: Python

rolling_formula_time

rolling_formula_time creates a rolling formula column in an update_by table operation for the supplied column names, using time as the windowing unit.

This function accepts nanoseconds or time strings as the reverse and forward window parameters. Negative values are allowed and can be used to generate completely forward or completely reverse windows. Here are some examples of window values:

  • rev_time = 0, fwd_time = 0 - contains rows that exactly match the current row timestamp.
  • rev_time = “PT00:10:00”, fwd_time = “0” - contains rows from 10m before through the current row timestamp (inclusive).
  • rev_time = 0, fwd_time = 600_000_000_000 - contains rows from the current row through 10m following the current row timestamp (inclusive).
  • rev_time = “PT00:10:00”, fwd_time = “PT00:10:00” - contains rows from 10m before through 10m following the current row timestamp (inclusive).
  • rev_time = “PT00:10:00”, fwd_time = “-PT00:05:00” - contains rows from 10m before through 5m before the current row timestamp (inclusive), this is a purely backward-looking window.
  • rev_time = “-PT00:05:00”, fwd_time = “PT00:10:00” - contains rows from 5m following through 10m following the current row timestamp (inclusive), this is a purely forward-looking window.

A row containing a null in the timestamp column belongs to no window and will not be considered in the windows of other rows; its output will be null.

Syntax​

rolling_formula_time(
  ts_col: str,
  formula: str,
  formula_param: str,
  cols: Union[str, list[str]],
  rev_time: Union[int, str],
  fwd_time: Union[int, str] = 0
) -> UpdateByOperation

Parameters​

ParameterTypeDescription
ts_colstr

The timestamp column to be used for the time-based windowing.

formulastr

The user-defined formula to apply to each group. The formula can contain a combination of any of the following:

formula_paramstr

The parameter name for the input column’s vector within the formula. If formula is max(each), then each is the formula_param.

colsUnion[str, list[str]]

The column(s) to be operated on. These can include expressions to rename the output (e.g., NewCol = Col). When left empty, the rolling formula operation is performed on all columns.

rev_timeUnion[int, str]

The look-behind window size. This can be expressed as an integer representing a number of nanoseconds, or a time interval string, e.g. “PT00:00:00.001” or “PT5M”.

fwd_timeUnion[int,str

The look-ahead window size. This can be expressed as an integer representing a number of nanoseconds, or a time interval string, e.g. “PT00:00:00.001” or “PT5M”.

Returns​

An UpdateByOperation to be used in an update_by table operation.

Examples​

The following example performs an update_by on the prices table using a rolling_formula_time operation to calculate a rolling some of squares of prices:

from deephaven.updateby import rolling_formula_tick, rolling_formula_time
from deephaven import empty_table

prices = empty_table(20).update(
    [
        "Timestamp = '2024-02-23T09:30:00 ET' + ii * SECOND",
        "Ticker = (i % 2 == 0) ? `NVDA` : `GOOG`",
        "Price = randomDouble(100.0, 500.0)",
    ]
)

formula_time = rolling_formula_time(
    ts_col="Timestamp",
    formula="sum(x * x)",
    formula_param="x",
    cols="SumPriceSquared_Time = Price",
    rev_time="PT10s",
)

result = prices.update_by(ops=[formula_time], by="Ticker")