DateTime values in Bulk Query results - difference between CSV and JSON formats

Question

I'm running Bulk API queries in Python, using both the simple_salesforce and salesforce_bulk libraries. I've encountered a confusing result when querying DateTime fields.

Given a simple_salesforce connection:

connection = Salesforce(
      instance_url=os.environ['INSTANCE_URL'],
      session_id=os.environ['ACCESS_TOKEN']
 )

executing a Bulk API query that includes DateTime fields returns integer values of unclear meaning rather than the expected ISO-format string:

connection.bulk.Account.query('SELECT Name, CreatedDate, LastModifiedDate, SystemModStamp FROM Account')

[{'CreatedDate': 1546659665000, 'SystemModstamp': 1546659665000, 'attributes': {'url': '/services/data/v38.0/sobjects/Account/0010R00000VsXkHQAV','type': 'Account'}, 'Name': 'ACME', 'LastModifiedDate': 1546659665000}, ... ]

Performing the same query with the REST API yields results that appear correct:

[OrderedDict([('attributes', OrderedDict([('type', 'Account'), ('url', '/services/data/v38.0/sobjects/Account/0010R00000VsXkHQAV')])), ('Name', 'ACME'), ('CreatedDate', '2019-01-05T03:41:05.000+0000'), ...

Note the expected, ISO-format DateTime value. Changing the API version from 38.0 to 44.0 does not affect the behavior.

Switching to salesforce_bulk, which offers more control, I find the exact same behavior in the raw JSON result (i.e., before it's decoded by Python). Note that simple_salesforce always executes queries in JSON mode.

  "attributes" : {
    "type" : "Account",
    "url" : "/services/data/v40.0/sobjects/Account/0013600001exbKeAAI"
  },
  "Name" : "SomeAccount",
  "CreatedDate" : 1520100090000

However, if I switch to executing the query via the CSV mode of the Bulk API, I get back the expected ISO date result:

job = bulk.create_query_job('Account', contentType='CSV')
batch = bulk.query(job, 'SELECT Name, CreatedDate FROM Account')
while not bulk.is_batch_done(batch):
    wait(5)

for result in bulk.get_all_results_for_query_batch(batch):
    result = io.TextIOWrapper(result, 'utf-8')
    print(result.read())

yields

"Name","CreatedDate"
"Amalgamated Industries","2018-10-24T23:15:19.000Z"

I'm familiar with the documentation on date format in inbound records to the Bulk API, but I can't find any documentation that sheds light on this behavior.

Answer 1

Not quite sure what the question is here. The two APIs presumably just took different decisions on how to represent DateTime and those decisions may not be well documented.

The "Z" date/time value strings are using:

Z is the zone designator for the zero UTC offset

and the numeric date/time values are the number of milliseconds since midnight 1 Jan 1970. Most APIs can convert such a value to an appropriate DateTime object.

In Apex it is:

Long longtime = 1341828183000L;
DateTime dt = DateTime.newInstance(longtime);
DateTime expected = DateTime.newInstance(2012, 7, 09, 3, 3, 3);
System.assertEquals(expected, dt);