11

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.

  • 4
    I wonder if those DateTimes are actually stored with .000 millisecond precision? – bigassforce Feb 11 '14 at 20:56
  • what do you mean by that? and what would that effect? – edgartheunready Feb 11 '14 at 21:26
  • 2013-12-03T01:13:09:001Z is greater than 2013-12-03T01:13:09:000Z, so if the .000 is stored and calculated, then it's possible you are confused because of a rounding issue... – Nathan Williams Feb 11 '14 at 21:45
  • 1
    All SOQL Datetime stamps are returned in UTC, if I recall correctly. Could this be your issue? – greenstork Feb 11 '14 at 22:06
  • 1
    Hello, edgar, can you remove the escaped quotes around the 'Z' in your format String? I think it will give you some more information. – Marty C. Feb 12 '14 at 11:59
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];
  • They might help, but I think the bigger problem is how I am retrieving, storing, and using datetime objects. – edgartheunready Feb 12 '14 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.