Working with datetimes

This time I want to point out some weird behaviours I have learnt with the practice about datetimes in Salesforce. Did you know them?

1. Datetime fields do not store milliseconds.

Currently datetime fields don’t support millisecond precision. Even if you work with Datetime class (which do support millisecond precision), when you store them in database the milliseconds are lost.

This is a problem if you need to work with a high time precision, that can be worked around in some ways, for example storing the Unix time in a number field.

You can vote this related idea if you feel it is an important fact.

2. Conversions between datetime objects and Strings

Concatenating a datetime object with a string will print GMT datetime, while String.valueOf function will print your time zome datetime. You can always use String.valueOfGmt method for obtaining the same result as when concatenating with string:

DateTime dt1 = DateTime.valueOf('2015-12-09 12:22:13');

System.debug('dt1=' + dt1);
System.debug('dt1String=' + String.valueOf(dt1));
System.debug('dt1GMTString=' + String.valueOfGmt(dt1));

// dt1=2015-12-09 11:22:13 --> GMT
// dt1String=2015-12-09 12:22:13 --> current time zone
// dt1GMTString=2015-12-09 11:22:13 --> GMT

For this reason, you have to bear in mind if your formatted datetime strings represent a date in GMT format or in your current timezone format when converting it back into a datetime object. Look at this examples:

DateTime dt1Converted = DateTime.valueOf(String.valueOf(dt1));
DateTime dt1GmtConverted = DateTime.valueOfGmt(String.valueOfGmt(dt1));
DateTime dt1GmtBadConverted = DateTime.valueOfGmt(String.valueOf(dt1));
DateTime dt1BadConverted = DateTime.valueOf(String.valueOfGmt(dt1));

System.debug('dt1Converted=' + dt1Converted); // This way of printing printed GMT, right?
System.debug('dt1GmtConverted=' + dt1GmtConverted);
System.debug('dt1GmtBadConverted=' + dt1GmtBadConverted);
System.debug('dt1BadConverted=' + dt1BadConverted);

// dt1Converted=2015-12-09 11:22:13 --> Correct GMT for the date I indicated
// dt1GmtConverted=2015-12-09 11:22:13 --> Correct GMT for the date I indicated
// dt1GmtBadConverted=2015-12-09 12:22:13 --> Incorrect
// dt1BadConverted=2015-12-09 10:22:13 --> Incorrect

3. Querying datetimes from database

Datetime field values are stored as Coordinated Universal Time (UTC). When one of these values is returned in the Salesforce application, it is automatically adjusted for the timezone specified in your organization preferences.

In our example we have modified a myObject__c record at 2015-12-09 12:22:13 (current time zone) which is the same as 2015-12-09 11:22:13 (GMT time zone). If you want to be able of querying the database filtering by this datetime, you can do it in the next ways:

// Statically
myObject__c obj = [SELECT Id FROM myObject__c WHERE myDateTime__c = :dt1];

// Dynamically
 myObject__c obj = Database.query('SELECT Id FROM myObject__c WHERE myDateTime__c = :dt1');

Easy up to this point, as binding the datetime variables does any conversion for you. But, be careful! If you want to specify a formatted String to your dynamic query, without binding the datetime variable, see the next example:

String DATETIME_DATABASE_FORMAT = 'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'';

String dt1Formatted = dt1.format(DATETIME_DATABASE_FORMAT); 
String dt2Formatted = Datetime.valueOf('2015-12-09 11:22:13').format(DATETIME_DATABASE_FORMAT);

System.debug('dt1Formatted=' + String.valueOf(dt1Formatted));
System.debug('dt2Formatted=' + String.valueOf(dt2Formatted));

//dt1Formatted=2015-12-09T12:22:13Z --> It's 12:22 in your current time zone, the time you stored!
//dt2Formatted=2015-12-09T11:22:13Z --> It's 11:22 in your current time zone, which means it's 10:22 in GMT.

myObject__c obj = Database.query('SELECT Id FROM myObject__c WHERE myDateTime__c = '+ dt1Formatted); // This query won't return any records!!

myObject__c obj = Database.query('SELECT Id FROM myObject__c WHERE myDateTime__c = '+ dt2Formatted); // This query will return the correct record.

So, be very careful. In order the query succeeds, you have to bear in mind that the datetime is stored in UTC and thus if you are going to filter dynamically, you must convert to GMT first!

dt1 could have been converted to GMT in the following ways:

OPTION 1:

String dt1FormattedGMT = dt1.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'', 'GMT');

OPTION 2:

TimeZone tz = UserInfo.getTimeZone();

DateTime gmtTime = dt1.addSeconds(-tz.getOffset(now)/1000);

String dt1FormattedGMT = gmtTime.format(DATETIME_DATABASE_FORMAT);

Thank you so much to my colleague Jesús González for discovering this behaviour!

Conclusions:

• Bear in mind that datetime fields do not store milliseconds. If you need such precision, consider another option!
• Bear in mind that concatenating a datetime with a String prints it in GMT format!
• For working with datetimes in GMT format, use always String.valueOfGMT and Datetime.valueOfGmt methods.
• For working with datetimes in your current time zone format, use always String.valueOf and Datetime.valueOf methods.
• Datetime fields are stored as UTC, but when returned, they will be automatically adjusted for your timezone.
• Be very careful when querying datetimes, try to bind datetime variables (:dt1) always. If it’s not possible, and you have to use formatted strings, convert always to GMT before querying.