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:
RULE Check SECONDARY
RECORD REC1
WHERE REC1.ENTITY = _JobName AND
REC1.TIME > _SavedTime
REFRESH 1 MINUTES
RETRY 1 TIMES WHEN_NO_MATCH
ACTION
! ----- Job has run successfully
SET _JobDone := 1
END_ACTION
END_RULE
RULE Check SECONDARY
RECORD REC1
WHERE REC1.ENTITY = _JobName
REFRESH 1 MINUTES
RETRY 1 TIMES WHEN_NO_MATCH
ACTION
WHERE REC1.TIME > _SavedTime
! ----- Job has run successfully
SET _JobDone := 1
END_ACTION
END_RULE
RULE Check SECONDARY
NUMERIC _CheckTime
RECORD REC1
WHERE REC1.ENTITY = _JobName
REFRESH 1 MINUTES
RETRY 1 TIMES WHEN_NO_MATCH
ACTION
SET _CheckTime := @REC1.TIME
END_ACTION
ACTION
WHERE _CheckTime > _SavedTime
! ----- Job has run successfully
SET _JobDone := 1
END_ACTION
END_RULE