Replaying soql with today/yesterday literals

Question

I have a cron job that DLs records using a jsforce query. It runs at 9:30UTC and attempts to DL all records in that day and then ALL of yesterday (so it basically should be a total of all records created in the last 33.5 hours)

The query is as such SELECT Id FROM Record__c WHERE CreatedDate IN (today, yesterday).

Is this query correct in doing what I think it is (getting everything from the last 33.5 hours of 9:30UTC)?

I suspect a record may not have been DLd. I'd like to "replay" this query, how can I substitute real datetimes in place of the today and yesterday literals (what format should it be in)?

Answer 1

today and yesterday are evaluated in the user's time zone, not UTC (unless, of course, the user is configured in UTC time zone). In general, you can choose to write literal dates in your query, which might look like:

dateField >= 2021-03-08T00:00:00.000Z and dateField <= 2021-03-08T09:30:00.000Z

Note that data replication is better done with getUpdated and getDeleted API calls. This replication API is designed to help you not miss records during replication, so you don't need to worry about your query missing records.

The typical reason why a record would be missed is because it is not yet committed to the database when you run the query. The getUpdated and getDeleted calls consider in-flight requests, so you'll never miss a record.

Once you call a replication API, you can then retrieve any fields you want with the retrieve call.

Note: the links above are for the REST API, but are also available as a SOAP API if you prefer.