Repository navigation
[BigQuery] FieldValue.getTimestampValue returns incorrect microseconds #3356
Description
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.
Activity
shollyman commentedon Jun 7, 2018
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 commentedon Jun 10, 2018
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 commentedon Jun 11, 2018
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 commentedon Apr 9, 2019
@shollyman, is there anything we can do with this issue on the client side? If not, let's please close this issue.
feat: add columnNameCharacterMap to LoadJobConfiguration (#3356)
docs: fix list num (#3356)
chore(main): release 2.50.0 (#3327)
deps: update dependency ubuntu to v24 (#3356)
chore: mpu client preview merge train