0

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.

2

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.

  • Hi Brian, thanks for the response. I am quite happy to consider an entirely different technique (we have an "old fashioned" UI that we really should re-implement using Streaming as this would avoid these sorts of scenarios). However, since that is a huge task we needed a straight forward approach for exposing incremental creates/updates. Use of FOR UPDATE isn't appropriate since we are doing a read transaction. In-flight updates can, I hope, be safely ignored since they can only be recorded as happening in the same second so would be retrieved on the next incremental request. Make sense? Phil – Phil W Oct 5 '18 at 20:34
  • Your other point about equality is an interesting one. I'm not sure how that was playing out in this scenario. The query against SystemModstamp is part of the over-all equation, but I also do some client side processing, comparing the timestamps to find the earliest and in this context milliseconds count. I'll have to think about that aspect and see where it fits with what I saw. Cheers. – Phil W Oct 5 '18 at 20:39
  • I note that what Brian says about the truncation to 000 for the milliseconds for comparison purposes in SOQL queries, it appears that this applies to all Datetime-based fields. I certainly found this to be the case with the few custom date/time fields I have tried it with (as well as SystemModstamp). I can go further and say that custom date/time fields get truncated when writing to the DB; if you re-query, they have lost their sub-second component. Thanks for the heads up! – Phil W Oct 8 '18 at 14:07
0

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".

  • you could also use a Number field on each sobject that had the #of ms in epoch (would have to be set by a before insert/update trigger) – cropredy Oct 4 '18 at 21:24
  • @cropredy, that is true though I would have to ask Salesforce to index it (the fact that SystemModstamp is automatically indexed is a significant reason to choose this approach). – Phil W Oct 5 '18 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.