[BigQuery] FieldValue.getTimestampValue returns incorrect microseconds

[martinstuder]

I have a BigQuery table with a timestamp column where some values are '9999-12-31 23:59:59.999 UTC' (no microseconds; i.e. TIMESTAMP_TO_USEC of that timestamp gives 253402300799999000). Retrieving such a timestamp using the Java BigQuery API (via FieldValue.getTimestampValue) results in 253402300799999008 being returned, i.e. with an additional 8 microseconds. I haven't seen this for other timestamp values yet.

[shollyman]

I suspect this may be related to how the BigQuery API represents TIMESTAMP values in the json API response for tabledata.list / jobs.getqueryresults, which is seconds since epoch in a potentially lossy floating point representation.

If the goal is having microsecond precision, its likely better to project the data as microseconds (integer) rather than timestamp.

In my quick testing, wire response is consistently 2.53402300799999E11 regardless of the sql dialect I use, but there may be some lossiness converting that in the library, as it returns timestamps to callers via conversion to usec:

return new Double(Double.valueOf(getStringValue()) * MICROSECONDS).longValue();

[martinstuder]

Ok, that does explain what's going on. It seems odd to me though to serialize a TIMESTAMP that way. Is there any particular reason for not serializing the usec directly and doing Long.valueOf(getStringValue()).longValue()?

[shollyman]

This does seem an odd representation in hindsight, which comes from BigQuery's initial public release period back in 2012 when this was a more common way of representing timestamps in web APIs. Its remained in this form to maintain compatibility for existing callers, as we'd want to change something like this with a major API revision, which hasn't occurred since that period.

[sduskis]

@shollyman, is there anything we can do with this issue on the client side? If not, let's please close this issue.