Help Center

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

nor this one:

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

Instead, use a local variable to store the time, so that time is compared using variables only:

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

Provide feedback on this article