NullCleanup function

The NullCleanup function frees any pages in a time series instance that contain only null elements in a range or for the whole time series instance.

Syntax

NullCleanup(ts          TimeSeries, 
       begin_stamp datetime year to fraction(5), 
       end_stamp   datetime year to fraction(5),
       flags       integer default 0) 
returns TimeSeries;

NullCleanup(ts          TimeSeries,
       flags       integer default 0) 
returns TimeSeries;

NullCleanup(ts          TimeSeries, 
       begin_stamp datetime year to fraction(5)
       flags       integer default 0) 
returns TimeSeries;

NullCleanup(ts          TimeSeries, 
       NULL, 
       end_stamp   datetime year to fraction(5),
       flags       integer default 0) 
returns TimeSeries;

ts
The time series to act on.
begin_stamp
The begin point of the range.
end_stamp
The end point of the range.
flags
Valid values for the flags argument are described in The flags argument values. The default is 0.

Description

Use the NullCleanup function to free empty pages from a time series instance in one of the following time ranges:

  • A specified begin point and a specified end point
  • The whole time series instance
  • A specified begin point and the end of the time series instance
  • The beginning of the time series instance and a specified end point

If the begin point of the range falls before the origin of the time series instance, an error is raised.

Returns

A time series with all the empty pages in the range freed.

Examples

Example 1: Free empty pages between specified begin and end points

The following example frees the empty pages in a one-day range on the specified day in the time series instance for the location ID of 4727354321000111:
UPDATE ts_data
SET meter_data = NullCleanup(meter_data,
        '2010-11-11 00:00:00.00000'
     ::datetime year to fraction(5),
        '2010-11-11 00:00:00.00000'
     ::datetime year to fraction(5))
WHERE loc_esi_id = 4727354321000111;

Example 2: Free all empty pages in the time series instance

The following example frees all empty pages in the time series instance for the location ID of 4727354321000111:

UPDATE ts_data
SET meter_data = NullCleanup(meter_data)
WHERE loc_esi_id = 4727354321000111;

Example 3: Free empty pages from the beginning of the time series instance to a specified date

The following example frees empty pages from the beginning of the time series instance to the specified end point in the time series instance for the location ID of 4727354321000111:

UPDATE ts_data
SET meter_data = NullCleanup(meter_data, NULL,
        '2010-11-11 00:00:00.00000'
     ::datetime year to fraction(5))
WHERE loc_esi_id = 4727354321000111;