Apex Code Development You need to sign in to do that
Don't have an account?
Matthew Ritter 17 API timestamps
When querying objects via the Bulk API, timestamps and dates are returned as millis not strings. This is inconsistent with other salesforce APIs. For timestamps this is not necessarily a problem since the conversion is straight-forward and all timezones are UTC-based. For dates, it is actively returning incorrect values. Our organization's default timezone is CST and we've verified that our dates are being stored as dates in CST. When the Bulk API returns millis, they are being returned as midnight UTC instead of midnight in our default time zone. Realistically, it should not be returning millis and indicating any time zone opinion at all since there is no time zone information stored on a date type. Anyone have some insights into this?
Based on my understanding, the APIs will always written date and time in UCT format and it is then transformed to locally suit the User's timezone. The following link also affirms the same - https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_reference_batchinfo.htm (https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_reference_batchinfo.htm" target="_blank)
As a good design practice it is always recommended that date and time values are shared in a global UTC format. The transformation to a local timezone for local time zones should always reside at the consumer thus offering better flexibility and control over the format of the information being displayed,
Hence you may need to use the local DateTime library classes to transform the time to a local timezone and display it. One thing to note though is you hold the UTC date time values and stamp them within Sobject fields i.e. the fields on a Salesforce object, the date and time values are displayed respecting the Salesforce User's timezone and local.
Hope that helps.
Please mark the thread as SOLVED and answer as the BEST ANSWER if it helps address the issue.
In our case it has been verified that the Date field(s) in question are being populated in CST for reporting purposes. There is DateTime field from which they are derived which stores the timestamp in UTC. It's easy enough to validate by simply finding a few rows that have a timestamp just past midnight UTC. The Date field, as expected is still the previous day. For example, our DateTime field is 2017-11-01T01:10:34Z, The Date field is 2017-10-31. In all APIs except for the Bulk API for the LeadHistory table, the value is returned as a string - for example "2017-10-31". This is the expected behavior.
If we use query via the Bulk API on the LeadHistory table, the NewValue and OldValue fields are *not* strings. They are millis. Worse, they are millis @ midnight UTC (2017-10-31T00:00:00Z) which is *not* the same value as is returned elsewhere. While I agree that storing date/time values in UTC is a good practice, for many purposes having a Some_Date_Field_cst__c that is derived from a timestamp field is fine to not store in UTC. This is a Date, not a ZonedDate - it should have no opinion regarding the time zone it is in. It doesn't have that knowledge. If we wanted it to have that knowledge, we would have used a DateTime.
The fact that the Bulk API on History tables (only history tables) is returning millis is a bug. It is reporting an unzoned Date field as a specific DateTime and it is the only api/object combination that does this.