Use of Timestamps in Analyst Where Clauses

Internal Timestamp Format

Timestamps are treated in an Analyst as hundredths of seconds. When doing calculations, it is sometimes necessary to take account of this when applying raw numeric offsets to timestamps or when saving a timestamp in a NUMERIC variable. For example, the following three Where Clauses are equivalent and obtain files used in the last 30 days:

WHERE @FILEDET.LASTUSED < CurrentTime - 30 DAYS

WHERE @FILEDET.LASTUSED < CurrentTime - 30-0:0:00.0

WHERE @FILEDET.LASTUSED < (CurrentTime - (30*24*60*60*100))

Even though timestamps are treated as hundredths of seconds when accessed as raw numeric values, the numeric value has 4 decimal places so microsecond resolution is retained.

Comparing Timestamps With Timestamps Stored in Variables

In a where clause, a timestamp field cannot be directly compared with a timestamp that has been stored in a local or global variable. If the value of a timestamp field has been previously saved in a variable, and this value needs to be compared with another timestamp field, then the new timestamp field must be stored in a variable for comparison.

This then allows timestamp variables to be compared successfully, since they will be in the same format, ie NUMERIC.

For example:

this rule won't work: