SOQL Date Literal "TODAY" for different geographical regions

Question

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.

Answer 1

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.

Answer 2

In your batch you can get the timezone of the region you are working with:

// Get the Timezones
Timezone tzNeeded = Timezone.getTimeZone('America/New_York');
Timezone tzUser = userInfo.getTimeZone();

Then you can get the value of the time in GMT:

    DateTime dtGMT = datetime.newInstance(system.now().year(), system.now().month(), system.now().day());

//Get the offset for the timezone you are using in hours

    Integer offset = tzNeeded.getOffset()/3600000;
    Integer offsetUser = tzUser.getOffset()/3600000;

    DateTime timeToCompare = dtGMT.addHours(offset + offsetUser);

then use that in your query.

So, if a record was created in EST at 01:00, GMT would be 05:00 and PST would be 22:00 the day before.

This will give you a compare date/time of 05:00 - 07:00 (-4 for EST and -3 for running user) or 22:00

Then set the end time to be 24 hours later.

You may have to tweak it a bit but you get the general idea....