Use DataFormatOptions to download timestamps with full precision

[tswast]

Is your feature request related to a problem? Please describe.

When fetching timestamp values, the tabledata.list and jobs.getQueryResults APIs return a floating point value by default.

This can cause unexpected floating point issues when rounding timestamps in SQL, such as with the TIMESTAMP_TRUNC(COL_TIMESTAMP, MILLISECOND) function.

Describe the solution you'd like

The backend API adds a formatOptions.useInt64Timestamp option.

• Wherever tabledata.list, jobs.query, or jobs.getQueryResults are called, set the formatOptions.useInt64Timestamp option to True.
• Wherever timestamps in rows API responses are handled, update it to use integer math to extract microseconds and seconds.
• Add system tests to verify that timestamps rounded to every digit of supported precision (e.g. 9, 9.9, 9.99, ..., 9.999999) are returned exactly as set in the table/query.

Describe alternatives you've considered

N/A

Additional context

Precision issues for the other direction (to BigQuery) for similar floating-point reasons: #392

[HemangChothani]

@tswast Do we need anything in .proto file here because when i am passing in query_paramter it throws an error.

[tswast]

Why are you passing this to query_parameter? These are API parameters (called extra_params in our client code, I believe)

[HemangChothani]

Yes , I am passing in extra_params like extra_parms['formatOptions']=Dataformat options(object)

…

On Mon, 23 Nov, 2020, 9:01 pm Tim Swast, ***@***.***> wrote: Why are you passing this to query_parameter? These are API parameters (called extra_params in our client code, I believe) — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#395 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMAR4JWAJL2SI4ZFD24YKPTSRJ537ANCNFSM4T3QUAZA> .

[HemangChothani]

When i pass the extra_parms['formatOptions']=Dataformatoptions (object) as mentioned in dcument, it throws an error like Invalid JSON payload received. Unknown name "formatOptions": Cannot bind query parameter. 'formatOptions' is a message type. Parameters can only be bound to primitive types.

python-bigquery/google/cloud/bigquery/client.py

Lines 3154 to 3168 in a1949ae

	params = {}
	if selected_fields is not None:
	params["selectedFields"] = ",".join(field.name for field in selected_fields)
	if start_index is not None:
	params["startIndex"] = start_index
	row_iterator = RowIterator(
	client=self,
	api_request=functools.partial(self._call_api, retry, timeout=timeout),
	path="%s/data" % (table.path,),
	schema=schema,
	page_token=page_token,
	max_results=max_results,
	page_size=page_size,
	extra_params=params,

[tswast]

@HemangChothani Please try the following:

• Does it work if you first serialize the DataFormatOption dictionary to JSON? e.g. json.dumps({"useInt64Timestamp": True})
• Does it work if instead of setting extra_parms['formatOptions'], you set extra_parms['formatOptions.useInt64Timestamp']?

[HemangChothani]

* extra_parms['formatOptions.useInt64Timestamp'] = True

This works fine, so opened a drat PR to verify the approach, after that i will add a system test regarding that.