Frequently a LiveView visualization is to show only a short recent period of data from a table with many hours or days or accumulated data. This period is called the "window" and it needs to move along with the most recently received data or clock time and let anything outside the period drop out of the query result.

At the start of a new streaming query, LiveView examines the data-set and identifies the rows that make up the result-set and returns these rows as a "snapshot" to the client. Then as row data changes the predicate is re-applied and the result-set adjusted by adding new rows, removing deleted rows, and removing any rows that no longer meet the predicate criteria. These adjustments become the "continuous" updates sent to the client for presentation.

What query syntax is available to present this as a window of time into the data?

Different ways to construct a moving-window query.

There are several ways to construct a moving-window query: 

A) Use a WHEN...BETWEEN query.

An example time-windowed query (from our JavaScript sample) is:
SELECT quantityRemaining, transactionTime FROM ItemsSales WHERE sku = 'Cell Phone' WHEN transactionTime BETWEEN now()-seconds(180) AND now()

The query must reference a timestamp field in the WHEN-clause and use absolute timestamp expressions, and one or both expressions need to use the now() function. No other expression types are supported for this predicate.

There's an interesting case where the rows of interest are OUTSIDE the moving time window, for example, to show rows that have not been updated in the last two days (rows that indicate that something should have happened and did not). In this case, the WHEN clause should be:
  WHEN transactionTime BETWEEN epoch() AND now()-days(2)
Everything else remains the same.
 

B) Use a transform into a secondary table and apply a deletion rule.

This alternative moves the selection logic out of the query statement and into the server using a transform application.

The transform application determines what rows are added to the table and deletion rules are used to trim old (out-of-window) data from the table. In this case, the transform application would examine the timestamp and drop any tuples (row data from the source table) which have a timestamp outside the desired time period. The transform can also send along a "delete" command using tuple field CQSDelete=true. The secondary table itself would also apply a deletion rule or other deletion mechanism to remove rows. There are a number of deletion strategies described in article (TIBCO Community): 
  What are the most frequently used ways to trim rows from a LiveView table

In this case, the display client would simply select all rows from the secondary table and therefore receive continuous updates for all addition, change, and remove activity (example: "select * from secondaryTable order by timestamp limit 30").
 

C) Use a client-side cache of rows.

This alternative moves the maintenance of the window into the client exclusively and rows are not managed in the table or the query.

The client can manage this itself by using a simple query (example: "select * from table order by timestamp limit 30") and keeping a local cache of results. When the timestamp is outside the window, the client removes the row from its cache. This solution requires the client to have complete control over the visualization so that it draws presentation data from the local cache and not directly from the LiveView API.
 

D) Dealing with Historical Data

There is a special case where the data is historical and used for playback where the timestamps supplied in the data-set are not current.

Option 1 - Replace the timestamp with now()

If the data's record-time can be replaced by now(), the data-source then controls the pacing and replaces the timestamp from the data with current wall-clock-time. In this case the "WHEN...BETWEEN" query may be used. Add a column to the table that has an insert/update rule that uses now() and use query (example) "WHEN MyInsertedTimeStamp BETWEEN now()-hours(2) and now()". There is a utility project available on TIBCO Community to assist with this: Playback for Queries and Data into TIBCO LiveView™ (wiki)

Option 2 - Use a simple query and manage the rows in the table

This is like option "B" above, but manages the data in the source table with a StreamBase EventFlow application instead of a transform into a second table.

The WHEN...BETWEEN query cannot be used when:

• the data MUST be stored in the table without changes to the timestamp, and
• the timestamp MUST be used as given for the client to determine what "now" is (in order to display the right portion of the data)

...because now() (wall-clock time) doesn't have any relation to the data.

Instead use a simple query and manage the rows in the table with an EventFlow application that queries for the most recent timestamp from the data and removes rows which are outside the window. This will have the effect of keeping the most recent rows active and removing at intervals those rows which should not be displayed.

In the EventFlow application used to manage the table contents, use a LiveView Query Adapter to read the latest timestamp:
  select max(transactionTime) from DataTable
This will be updated when the maximum time is changed by incoming data. Then use the LiveView Delete Adapter to regularly delete records that are too old.