Filter a Thingworx DataTable

In a previous post I detailed how and why to use a DataTable in Thingworx. As soon as you create a DataTable you will need to search and filter its contents.

So, lets dive into filtering first. Filtering tables in Thingworx (at least in version 8) is very clunky from a developer perspective. It requires writing a literal JSON string in the JavaScript service code editor and assigning it to a variable. One could argue queries are the poster child for having snippets. They are not something anyone is going to remember how to write. Fortunately there is a snippet for Stream, Blog, Data Table listed below the one for InfoTable that partially work:

The default form of a filter is provided, but even in the DataTable snippet the DataShape field names are not used. There is no “name” field in the DataShape I selected. But it is still better to start with the correct JSON format and build from there.

To test filtering I created a service in a “sandbox” Thing entity where I keep POC services. The use case only required retaining a small number of days worth of rows. Purging the oldest day records daily is facilitated by a date-time stamp to filter date ranges. I find that creating date range filters is particularly difficult in Thingworx, so I stored the dates as numbers holding the number of milliseconds in the Unix Epoch.

Without enough records to filter rows as old as would be required by the use case, substitute a much smaller interval. Hour or minutes will confirm that the query works predictably. To test against a certain hour Date.parse should work with any readable input. Thingworx services require a very specific date format though:

var timeFilter = Number(Date.parse('Fri Aug 18 2023 21:15:00 GMT-0400 (EST)'));

Filtering a date-time range will require inputting the time criterion in the format shown in the examples above and converting it to milliseconds before assigning it to the filter JSON string. That is why the result is assigned to a variable timeFilter

Note that for displaying the timestamp value in logs and DateTable rows, use toString() in place of Number() to remove scientific notation from the result. Set the field type to be Number in the DataShape, not DateTime.

Date.parse('Fri Aug 18 2023 21:15:00 GMT-0400 (EST)').toString()

The screenshot below shows the scientific notation format in the key column for the same timestamp value as timestamp column. The dateTimeReceived shows the String representation of the same value – zeroes are shown in place of the E12.

Use variables to store millisecond values for common time units:

var newDate = new Date();
var now = Number(Date.parse(newDate));
// or if you want to start at midnight instead of relative to now...
newDate.setHours(4);
newDate.setMinutes(0);
newDate.setSeconds(1);
var midnight = Number(Date.parse(newDate));

var oneHour = 60 * 60 * 1000;
var oneDay = 24 * oneHour;
var oneWeek = 7 * oneDay;

Using this approach one can define filters that are easy to read and comprehend within the clunky JSON literal syntax, such as this:

query = {
	"filters": {
        "fieldName": "dateTimeReceived",
        "type": "GT",
        "value": now - oneHour /*1692392610000 */
    }
};