SOQL Date functions are pretty useful for grouping or filtering data by date fields. With a proper Date function used in the SOQL, the code can potentially limit the query result records a lot. e.g. Query all Tasks that are created today:
List<Task> = [
select Id, WhatId, Subject, CreatedDate
from Task
where DAY_ONLY(CreatedDate) = :Date.today()];
The above code looks neat enough although the function DAY_ONLY is not that obviously named. The documentation states: "Returns a date representing the day portion of a dateTime field." so it should be safe enough. I used it in a few places and it worked very well. However recently I got a failing unit test while I was creating a managed package. The unit test was testing the logic that uses the above code. The test only fails when it runs after 5pm GMT-7 and before 12am GMT-7. The local time zone of the org is GMT-7.
I started debugging the issue in that precious 7-hour window period. I created a sample Task after 5pm GMT-7 and ran the above code immediately and it returned no records! I changed “Date.today()” to “Date.today() + 1” in the where clause, re-ran the code and it returned the Task I just created. What? Is this saying “Get me all Tasks created tomorrow”? Obviously not, the only explanation on this is that the DAY_ONLY() function takes Datetime parameter as GMT time. Any time between 5pm GMT-7 and 12am GMT-7 is already tomorrow’s time GMT.
All of a sudden I realized that all those Date functions like CALENDAR_YEAR, DAY_IN_MONTH etc. could be useless as they are all having the same timezone issue. That cannot be right. I got back to the documentation and found it has this statement:
“SOQL queries in a client application return dateTime field values as Coordinated Universal Time (UTC) values. To convert dateTime field values to your default time zone, see Converting Time Zones in Date Functions.”
On the “Converting Time Zones in Date Functions” web page, it says “You can use convertTimezone() in a date function to convert dateTime fields to the user’s time zone.”. What do you mean by “You CAN…”? In an org with a different timezone other than GMT, you HAVE to use this method to make it working! This is obviously a compromised solution of fixing the original timezone issue in those Date functions.
Generally speaking, the timezone issue is introduced because in Apex, Datetime and Date are not clearly differentiated and Salesforce badly handles these two types. See some other Datetime issues in this blog post: Danger: Date value can be assigned to a Datetime variable.
Anyway, the fix of the above code is:
List<Task> = [
select Id, WhatId, Subject, CreatedDate
from Task
where DAY_ONLY(convertTimezone(CreatedDate)) = :Date.today()];
Force 746 