It appears that > behaves like >= in a soql query. Why?

Question

The query:

SELECT systemmodstamp, id 
FROM Lead 
WHERE SystemModstamp > 2013-12-03T01:13:09Z 
AND SystemModstamp < 2014-02-11T12:18:41Z 
ORDER BY SystemModstamp ASC ALL ROWS

returns several Lead SObjects with a SystemModstamp of: 2013-12-03T01:13:09Z

Why? I would assume that the query above would include only Leads with a SystemModstamp of 2013-12-03T01:13:10Z or greater.

Update: I updated the string formatting of the SystemModstamp from the result set, and it looks like the records do not have ms values. This apex:

//obj is a record returned from the query
//timestampField is a string with the value of 'SystemModstamp'
((DateTime)obj.get(timestampField)).format('yyyy-MM-dd\'T\'hh:mm:ss:SSS\'Z\'')

rendered the following output:

2013-12-03T01:13:09:000Z

Another Update: Could this be caused by Timezones? Looks like the datetime objects I am using in my query are in PST.

Another Update: '>' will never behave like '>='. It appeared to because a of timezone issue. When i work the bugs out of my code, I will post an answer.

My particular use case follows this flow:

• run a query retrieving all objects modified after a given date (by default 90 days in the past)
• store the largest SystemModstamp from the result set in a custom setting (as a DateTime)
• run another query retrieving all objects modified after the date stored in the custom setting
• update the custom setting with the largest SystemModstamp from the newest result set.

my query does not use a formatted datetime string, but instead uses variable binding.

Last Update: I don't know what was going on, but my code is working now. I have not solution, and can't reproduce the issue now. Is there a way to delete this question because I don't know what the solution is. The main point of this whole question has become Timezones. When you format a string it automatically converts the datetime to the timezone of the SF instance. (In my case PST). It could have been that in the process of debugging my code i switched from from bound datetime values in my soql query to string datetime values, and in so doing created the issue I was experiencing.

Answer 1

If you need to create GMT DateTime objects you can try the following methods:

You can use raw numbers to create a DateTime object.

// Create your DateTime reference point using
// DateTime.newInstanceGmt(Integer, Integer, Integer, Integer, Integer, Integer)

DateTime referenceDate = DateTime.newInstanceGmt(2013, 12, 3, 1, 13, 9);
List<Lead> leads = [SELECT Id FROM Lead WHERE SystemModstamp > :referenceDate];

Alternatively, you can use a String to create a DateTime object.

// Create your DateTime reference point using DateTime.valueOfGmt(String)

DateTime referenceDate = DateTime.parse('2013-12-03 01:13:09');
List<Lead> leads = [SELECT Id FROM Lead WHERE SystemModstamp > :referenceDate];