We have a scenario where we need to compare a date time field Last_Response_Date__c
against today function in SOQL
List<Contact> lstContactWithSamePhoneNo1 = [SELECT
Id,Last_Response_Date__c,End_Monitoring_Date__c,todayvalue__c
from Contact
where
Id ='123' AND (Last_Response_Date__c < TODAY)];
Our understanding was SOQL always run in UTC when date time fields are queried so it would compare UTC value of Last_Response_Date__c
value against today
in UTC and return the results.
Below is the record we are trying to query:
(Contact:{Id=123, Last_Response_Date__c=2020-05-20 00:13:06,
End_Monitoring_Date__c=2020-05-25 00:00:00})
Here is the issue below when the SOQL runs for any user:
- when we set users timezone to
"(GMT) Africa/Casablanca"
it doesnt return the record in the results - When we set users timezone to
"(GMT-04:00) Eastern Daylight Time(America/New_York)"
this record is returned in the results
Question:
- If the comparison is based on GMT why is it returning records in the
second case above when set to EST timezone and not when set to GMT
timezone? - Does the results mean salesforce implicitly converts
today and Last_Response_Date__c
to user's time zone when the SOQL is comparing in the where clause and returns results.
Any inputs will be appreciated.
Best Answer
All date literals return values based on the user's current time zone. This ensures that if a user asks for records from "today" or "last_n_days:10", they'll get results that they would expect to get if they were in their time zone.
The Last_Response_Date__c isn't converted, it's just that the date literal returns a GMT window that matches the expected values for the user's time zone. For example, if they're in EST, then TODAY is converted to 19:00:00-18:59:59, and the database is queried using that time range. This all happens behind the scenes so the developer can easily answer questions the user cares about without manual time zone calculations. Note that the times are still returned in GMT, so conversion of the values may be needed (Visualforce and Lightning UI elements do this automatically, but other clients may need to do conversion).