Using SystemModstamp and a timestamp to implement incremental data fetch: gotcha!

Question

Each time an object is created or updated it has the LastModifiedDate and SystemModstamp values set or updated. There is a separate question regarding the different between these two, covered here. What I'm concerned about is the actual precision to which SystemModstamp is held.

It is a Datetime value, and therefore should be able to be measured to the millisecond (after all, Datetime.getTime() returns values in milliseconds since epoch).

It seems, in principle, that an object's SystemModstamp could be compared against a "timestamp" obtained via System.now().getTime() in order to find objects created or updated after a given time (the "timestamp" having been captured at the time the previous query was made). This doesn't actually always work in practice.

Answer 1

The system currently ignores the milliseconds for the purposes of considering two values equal. Here's some code you can run in your org to prove it to yourself:

User u = [SELECT SystemModStamp FROM User ORDER BY SystemModStamp DESC LIMIT 1];
DateTime dt = u.SystemModStamp;
// add 900 milliseconds
dt = DateTime.newInstanceGMT(
  dt.dateGMT(),
  Time.newInstance(dt.hourGMT(), dt.minuteGMT(), dt.secondGMT(), 900));
User u2 = [SELECT SystemModStamp FROM User WHERE SystemModStamp >= :dt];
// They are equal despite being 900 milliseconds apart
System.assertEquals(dt, u2.SystemModStamp);

So, what happened to the records that your code was periodically missing? The answer is that you most likely did not include any row-locking statements, so the records in question were still "in flight" and couldn't be found by the query.

Depending on what you're doing, there's several possible solutions to the problem. You may need to use FOR UPDATE if you're using a SOQL in Apex to make sure that you capture any in-flight records. If you have an integration, you may want to use the Replication API, which accounts for in-flight modifications to make sure you don't miss records in a given window. For Batchable, where you can't use row-locking statements, you may simply need to add an acceptable amount of offset to make sure that you don't miss records, but you may need to account for duplicates being processed.

In the end, it's probably best that you avoid relying on SystemModStamp directly, or avoid running queries that may miss in-flight records (e.g. by running to the nearest millisecond). The problem is not with the precision of the audit fields, since milliseconds are always ignored.

Answer 2

I found that SystemModstamp is always rounded down to the start of the second in which it is set. Whilst the value is representable in milliseconds in Datetime, in practice the last three digits for obj.SystemModstamp.getTime() are always 000.

I point this out because System.now().getTime() actually returns values to the millisecond, which are therefore not directly compatible as a comparison against the SystemModstamp.

Imagine generating a "timestamp" using System.now().getTime() that is returned to an external application as a "token" during query invocation that can be returned to Salesforce when performing a later query for newly created or updated objects is made. Such a timestamp/token would be used in the query as an SOQL WHERE clause like "SystemModstamp >= :token".

This could fail to return all updated/new objects from the database since the SystemModstamp against objects is rounded down and the "timestamp" is not by default. Any objects created in the second where the timestamp was captured would appear to be created earlier than the timestamp (unless the timestamp happened exactly at the start of the second).

The solution is to match SystemModstamp's behaviour by rounding down the generated "timestamp".