[SalesForce] SOQL Date Literal “TODAY” for different geographical regions

I have a tricky problem. I have written 3 Apex batch/scheduling jobs, one for North America, European Union, and Asian Pacific. Each job runs the following SOQL query to get any Asset records that were installed "Today" in that region:

Select id from Asset where InstallDate = TODAY

The problem I am having is with the TODAY date literal. I have confirmed that it takes the context of the user who schedules/runs the batch job. Ie, when the job is scheduled by me, a North America user, the TODAY variable is in PST (Pacific Standard Time).

To have the query return the correct results, we would have to create fake users for EU and APAC (NA would work fine) to schedule the jobs with. We dont want to waste 2 Salesforce licenses, but cant think of another solution. Very open to clever ideas, thanks.

Best Answer

I managed to get something working that might solve your problem.

Select id from Asset where DAY_ONLY(InstallDate) = :DateTime.now().addHours(<x>).date()

Where <x> is the difference in hours between the time zone of the user that schedules/runs your batch job, and the time zone of your target region

From the documentation on SOQL date functions, it says that you can use a SOQL date function on a field in the WHERE clause of the query without needing a GROUP BY clause.

It goes on to say that you can't use a SOQL date literal (such as TODAY), to compare against, but it doesn't say anything about not being able to bind an apex expression to compare against.

Hard-coding the timezone difference in apex doesn't sit too well with me. You could substitute hard-coding with using a custom setting in your org. A custom metadata type would probably work as well.

Related Topic