SOQL where clause for date time fields - user timezone or UTC?

Question

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:

1. 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?
2. 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.

Answer 1

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?

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.

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.

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