CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sqlboomi
Cristian Baciu
Cristian Baciu

Reputation: 163

How to format a float64 date returned by a bigQuery job to timestamp/date?

What I am trying to do is create a job using this query:

SQL Query:

SELECT execution_id, top_level_execution_id, process_id, atom_id, message, node_id, pay_load, environment_name, status, date_created, custom_message, dynamic_field1, dynamic_field2, dynamic_field3, RecordID FROM [boomi.process_errors]

After that, running the job using the generated ID and getting the results.

JSON used for creating the job:

{
   "configuration":{
      "query":{
         "defaultDataset":{
            "datasetId":"XXX",
            "projectId":"XXX",
            "tableId":"process_errors"
         },
         "query":"SELECT execution_id, top_level_execution_id, process_id, atom_id, message, node_id, pay_load, environment_name, status, date_created, custom_message, dynamic_field1, dynamic_field2, dynamic_field3, RecordID FROM [boomi.process_errors]"
      }
   }
}

Sample returned record:

{
   "execution_id":"execution-c5723421-0fd9-47e3-9241-3520dfebf21d-2021.02.08",
   "top_level_execution_id":"execution-c5723421-0fd9-47e3-9241-3520dfebf21d-2021.02.08",
   "process_id":"b8fb913d-26da-40bc-bce4-11bf65db03db",
   "atom_id":"eae5dcd2-af12-4c99-805e-12ae5bc5a5c5",
   "message":"Error Messae\n",
   "node_id":"atom01",
   "pay_load":"TBD",
   "environment_name":"TEST",
   "status":"ERROR",
   "date_created":"1.612789722974E9",
   "custom_message":"Error hapened in Process",
   "dynamic_field1":"abc123",
   "dynamic_field2":"bca123",
   "dynamic_field3":"abc321",
   "RecordID":null
}

As you can see, the date is FLOAT64: 1.612789722974E9. When I am trying to format the date to timestamp from the bigQuery console, it is already coming in as timestamp: 2021-02-08 13:08:42.974 UTC.

What I have tried to do is use this query instead:

SELECT execution_id, top_level_execution_id, process_id, atom_id, message, node_id, pay_load, environment_name, status, CONCAT(DATE(USEC_TO_TIMESTAMP(CAST(date_created AS INTEGER))), 'T', TIME(USEC_TO_TIMESTAMP(CAST(date_created AS INTEGER)))) as date_created, custom_message, dynamic_field1, dynamic_field2, dynamic_field3, RecordID FROM [boomi.process_errors]

Which will get me the date and time, but not the milliseconds. I thought time returned milliseconds as well. Tried using various functions from the bigQuery library and none of them work on float64.

As an aside, I am using Dell Boomi as middleware layer for creating the job, retrieving the job ID and running it. I need to automatically create a report based on what is selected from this table, so that is why I need the Datetime, including milliseconds.

I thought you could add something to the configuration that you use for the job creation so that dates are returned in normal timestamp format, but again, found nothing.

Even in the BigQuery console it says it's a timestamp, so why is it stored as an FLOAT64? enter image description here

Upvotes: 3

Views: 421

Answers (1)

Cristian Baciu
Cristian Baciu

Reputation: 163

Nevermind, it is too complicated. I changed the column to datetime and it works.

Upvotes: 1

Related Questions