Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime

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()];

	

Published by Chun Wu

When nothing is certain, anything is possible.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: