search

michael-shattuck / SalesforceMagic

Easy access to various Salesforce APIs.
http://michael-shattuck.github.io/SalesforceMagic/
MIT License
26 stars 15 forks source link

DateTime conversions yield inconsistent results because of UTC #14

Open zendever opened 9 years ago

zendever commented 9 years ago

When using DateTime fields, input DateTime are saved in the yyyy-MM-ddTHH:mm:ssZ format, but if the DateTime isn't converted to UTC before saving, the value of the same object returned by a SOQL query will yield a different date. This is because the expected input and output of Salesforce is UTC, and a conversion to UTC doesn't take place automatically on the input, but is automatically converted to local time on the output.

The workaround, of course, would be to convert all dates to UTC before saving, and expect local times on the return.

It would be nice to have the expected input match the expected output from Salesforce. Thus, if I send in a local time, the conversion would automatically set the correct UTC date, and then a subsequent SOQL query would return the same local time.

Simple test would be to: 1) Create an SObject with a date 2) Set the date field to a local date 3) Save the SObject 4) Query the SObject by ID to get a copy of the object 5) Compare dates

The test will fail if there isn't an explicit conversion after step 4 to convert the queried date back to UTC.

This does beg a larger look at a larger design feature. The automatic conversion at the back end is somewhat problematic once you start working with dates outside of your local time. Thus, if you serialize an object from another system that has a different timezone, then the date has to be set to an offset of your local time, and the test above would fail again. The work around for this, of course, would be to convert the dates to prior to a save and after a query, but a similar problem of tedium exists.

michael-shattuck commented 9 years ago

This is an excellent point. Perhaps the flow needs to be revisited.

benbenwilde commented 9 years ago

for reference - https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

benbenwilde commented 9 years ago

If it's a DateTime - it would be nice to assume that all DateTime objects are UTC as you do today, and use the UTC format as you do today: YYYY-MM-DDThh:mm:ssZ. That leaves the timezone issues up to the user. It would be dangerous if a user had a DateTime in UTC but accidentally had the DateTimeKind set to local. Seems safer to assume that it's UTC as most users would probably only send a DateTime in as UTC knowing that they are working with a database that could be accessed from any time zone, and also knowing that DateTime does not have time zone data.

DateTimeOffset, however is a different situation. If its set to a timezone, we can use the offset format YYYY-MM-DDThh:mm:ss+hh:mm or YYYY-MM-DDThh:mm:ss-hh:mm, and if it is UTC, then YYYY-MM-DDThh:mm:ssZ as with DateTime.