1

I have a Cloud Function that connects to the Salesforce Bulk API with a select query that returns the data in JSON which then uploads to a Google Cloud bucket. The CF itself works with no issue, however the field with a date in it returns the date in milliseconds.

I'm looking for a way to convert this date from milliseconds to a readable datetime format.

def test_query():

  job = bulk.create_query_job("Person", contentType='JSON')
  batch = bulk.query(job, "SELECT Name, Id, Date FROM Person")
  bulk.close_job(job)
  while not bulk.is_batch_done(batch):
    sleep(10)


  for result in bulk.get_all_results_for_query_batch(batch):
    result = json.load(IteratorBytesIO(result))
    for row in result:
        print(row)# dictionary rows

  # Push data to GCS
  blob = bucket.blob('PersonObj.json')
  json_string = json.dumps(result)
  blob.upload_from_string(json_string)

Result of one line of JSON

"Name": "John Smith",
"Id": "0011E01231fAn1BWTF",
"Date": 1550602301000

Looking to have "Date" be returned as something readable such as yyyy-mm-dd hh:mm:ss.s

Perhaps this is something that can be done using the CSV method or in BigQuery as this is ultimately where I intend to use this data.

Thank you!

a_flid
  • 11
  • 3
  • https://blog.softhints.com/python-convert-date-unix-timestamp/ , https://stackoverflow.com/questions/66513597/how-to-convert-millisecond-unix-timestamp-to-readable-date (answer loses ms) – user2864740 Jan 17 '22 at 22:54
  • Okay, this is a bit silly. I’ve been unable to locate a true duplicate that *keeps* milliseconds; however, I’m sure such exists. – user2864740 Jan 17 '22 at 22:58
  • In the example value given (1550602301000), there appears to be *no millisecond* precision of interest. If there are *expected* to be millisecond information to preserve (and should be preserved), choose a different value where such could be lost when decreasing the precision. This added details may impact viable answers. – user2864740 Jan 17 '22 at 23:07
  • 1
    I'm more so looking for a readable date format as opposed to this long integer that I'm receiving back currently. Less concerned with millisecond precision, but instead just trying to make sense of this date. – a_flid Jan 18 '22 at 15:02

1 Answers1

1
jsonBlob = {"Name": "John Smith",
"Id": "0011E01231fAn1BWTF",
"Date": 1550602301000}

datetime.fromtimestamp(jsonBlob["Date"]/1000.0)

Not sure if 2019-02-19 12:51:41 is the actual date due to not having actual data. Perhaps solve with above

Jatin Morar
  • 164
  • 1
  • 7
  • This *loses* any milliseconds, should they appear (in this case no millisecond information was provided in the example value). This potential loss of precision should be included in such answers, as a note — Python datetime objects themselves have microsecond precision. – user2864740 Jan 17 '22 at 23:01
  • Specifically the OP calls out “milliseconds” in the title and shows a format of `hh:mm:ss.s`. With the truncation to seconds, this would always end as ".0". – user2864740 Jan 17 '22 at 23:04