Hertz time series

If your time series data is recorded at a regular subsecond frequency, you can define a hertz time series to store the data efficiently.

In each element, a hertz time series stores an 11-byte time stamp for the first record and a 1-byte time stamp for each of the other records. An element contains records for one second. For example, if data is recorded 5 times a second, then each element contains 5 sets of values. If the TimeSeries subtype contains a time stamp column and two other columns, the following table shows how the values are stored in each element.

Table 1. How hertz data is packed in time series elements
Timestamp of element Values for .00000 seconds Values for .20000 seconds Values for .40000 seconds Values for .60000 seconds Values for .80000 seconds
2014-01-01 00:00:00.00000 1.01, 0.25 1.93, 0.11 1.74, 0.02 1.03, 0.45 1.85, 0.44
2014-01-01 00:00:01.00000 2.00, 0.02 1.99, 0.05 1.53, 0.03 NULL, NULL 1.76, 0.01

Data requirements

Hertz records must have time stamps that conform to the hertz subsecond boundaries within approximately 0.3% tolerance. Only one record per subsecond boundary is allowed. For example, if the hertz value is 5, then there are 5 valid time stamps per second. For the first second after midnight, the following time stamps are valid:

  • 00:00:00.00000
  • 00:00:00.20000
  • 00:00:00.40000
  • 00:00:00.60000
  • 00:00:00.80000

The tolerance means that a time stamp has a margin of error of plus or minus 0.00003 seconds. For example, for the second record, a subsecond value of 0.20003 is accepted and stored as 0.2, but a subsecond value of 0.20004 is rejected.

If the value of a subsecond boundary has more than 5 significant digits, the value is rounded down. For example, the 127th subsecond value for a hertz of 255 has the value 0.498039216, which is rounded down to 0.49803.

NULL values are allowed.

The size of the data for 1 second cannot exceed 32769 bytes.

You can enter whole-second blocks of elements out of chronological order, but subsecond elements within a second must be entered in chronological order. For example, you can enter the following elements in the order shown:

2:00:00 00.00.01.00000
2:00:00 00.00.01.02000
2:00:00 00.00.00.00000
2:00:00 00.00.00.02000

However, you receive an error when you attempt to enter subsecond elements out of order, for example:

2:00:00 00.00.01.02000
2:00:00 00.00.01.00000

Time series definition

The TimeSeries subtype that you define for hertz data must have columns of only the following data types: SMALLINT, INT, BIGINT, SMALLFLOAT, FLOAT, DATE, INT8, CHAR, VARCHAR, NCHAR, NVCHAR, LVARCHAR, DATETIME, DECIMAL, and MONEY.

You can use a calendar with any interval size because the interval is significant only in defining on and off periods. The interval size for a hertz time series is defined by the hertz parameter when you create the time series.

You define a hertz time series by running the TSCreateIrr function with the hertz parameter.

Hertz data can be stored in rolling window containers.