SOQL Date literal TODAY is evaluated incorrectly

[AndreyVol]

I am having the following problem. When I use TODAY literal in my SOQL statements comparing with ActivityDate for some reason the records where date = tomorrow are returned. Here are the details:

Code:

Select e.ActivityDate, e.ActivityDateTime, e.Id, e.Subject from Event e  
Where e.OwnerId = '<myId>'  AND e.ActivityDate = TODAY 

Today is 05/18/2007


The statement does not return events where ActivityDate is 05/18/2007. Instead in returns events where ActivityDate is 05/19/2007.

When I filter by a Datetime field (Event.ActivityDatetime) this does not occur and correct records are returned.


For now, I just simply modified my SOQL to use YESTERDAY instead of TODAY. But I would like to know the reason for this behavior. Am I missing something here or this could be an issue?


Thank you.

[sfdcfox]

All of the date literals are offset by the user's time zone settings. If you don't live in London, the API will return dates that appear to be outside of the date literal (eg. TODAY returning items for tomorrow), but they're really not (because the times are returned in GMT). For example, if you live in California, and you choose TODAY, and you have an activity for 5/23/2007 5pm, the date/time returned will be 5/24/2007 1am. Which is tomorrow in London (GMT), but it's today for you, since you're in California. This also applies to LAST_N_DAYS, THIS_MONTH, etc. The results will always be correct for the user running the query, which will be different for each user. Use GMT values if you would prefer to get consistent results across all users.

~ sfdcfox ~

[AndreyVol]

When I run queries against datetime fields, the TODAY literal works just as expected. My API user time offset is configured correctly for my timezone and all the dates are returned as expected.

The problem occurs when I query Date fields (not Datetime). So, it appears that Date fields are not offset by the user settings as Datetime fields are... Instead they are returned always in GMT. To me this seems inconsistent...

Message Edited by AndreyVol on 05-24-2007 06:35 PM