Select records based on the last modified timestamp

Select records using the last modified datetime to limit the selection to data posted to the Data Lake within a specific time frame.

The lastModified value represents the timestamp, in UTC, for the datetime in which the data object was added to the Data Lake. This syntax is useful because you can append the lastModified condition to retrieve data objects loaded into the Data Lake based on the object's load time. This is the main difference between selecting objects using the standard Birst object selection and a query-based selection.

The timestamp is a property of the object itself. It is not a property inside of the object payload. Through Birst, you can setup a variable to define the timestamp value, which can be used in place of specifying a timestamp value. Enclose the timestamp value, or the variable, in single quotes. The lastModified timestamp format is a timestamp in UTC. The timestamp format is: yyyy-MM-dd'T'HH:mm:ss.SSS'Z' (For example: 2021-07-16T19:23:51Z)

A last modified reference in a query expression’s WHERE clause can use these operators:

  • =
  • >=
  • <=
  • BETWEEN

Syntax

SELECT ... from object where infor.lastModified() >= 'Timestamp in UTC';