losing precision in converting TIMESTAMP and INT64 to Javascript Number

[c0b]

https://github.com/GoogleCloudPlatform/google-cloud-node/pull/1648#issuecomment-251383826

The BigQuery TIMESTAMP has up to microseconds precision, but when converting to a JavaScript Date, it becomes up to milliseconds

https://github.com/GoogleCloudPlatform/google-cloud-node/pull/1648#issuecomment-251249300

A JavaScript Number is really only a FLOAT64, there is no real INT64, so during conversion some precision is lost:

$ node ./bigquery/queries.js sync 'SELECT ARRAY<INT64>[0x7fff1234deadbeef, -0x8000000000000000] AS example_array'
{ err: null,
  rows: [ { example_array: [ 9223110580161593000, -9223372036854776000 ] } ],
  nextQuery: null,
  apiResponse: 
   { kind: 'bigquery#queryResponse',
     schema: { fields: [ { name: 'example_array', type: 'INTEGER', mode: 'REPEATED' } ] },
     jobReference: { ... },
     totalRows: '1',
     rows: [ { f: [ { v: [ { v: '9223110580161593071' }, { v: '-9223372036854775808' } ] } ] } ],
     totalBytesProcessed: '0',
     jobComplete: true,
     cacheHit: false } }
Received 1 row(s)!
[ { example_array: [ 9223110580161593000, -9223372036854776000 ] } ]

I don't really have a solution, please suggest when application need this much precision

[stephenplusplus]

Thank you for opening this, @c0b! I think we're okay on integer & timestamp precision, since it is available through the raw apiResponse argument when the user requires it.

@callmehiphop what do you think?

[callmehiphop]

Maybe in the docs we could recommend/show examples for precision using an int64 lib? We use node-int64 in Bigtable.

[lukesneeringer]

Based on the discussion in the Node.js standup today, we are deciding this is not release blocking. A user who needs the full precision can get it from the raw API response in the third callback argument.

[stephenplusplus]

I don't believe there is a good solution for this without introducing complexity.

TIMESTAMP

The docs say that TIMESTAMPs are stored internally with microsecond precision, however, the raw API response seems to be returning the value in seconds.

INT64

The solution for this would be a bit more complex for the user. Currently, if you read one of these values, you get the native JS "Number" type. For our @google-cloud/spanner API, the Spanner team wanted to guarantee the same precision the API stores, so this is how the "more complex" solution looks for that API: https://github.com/googleapis/nodejs-spanner/blob/8a8b43f52afdbcd443cdc6d6c5d0f2fa68ee5566/src/codec.js#L54-L66:

function Int(value) {
  this.value = value.toString();
}

Int.prototype.valueOf = function() {
  var number = Number(this.value);

  if (number > Number.MAX_SAFE_INTEGER) {
    throw new Error('Integer ' + this.value + ' is out of bounds.');
  }

  return number;
};

table.read(query, function(err, rows) {
  var row = rows[0]

  row = [
    {
      name: 'SafeInt',
      value: {
        value: '2' // string
      }
    },
    {
      name: 'OutOfBoundsInt',
      value: {
        value: '--out-of-bounds-integer--' // string
      }
    }
  ]

  var safeInt = row[0].value
  typeof safeInt === Spanner.Int
  console.log(safeInt.value)
  // '2' (String)
  console.log(safeInt)
  // 2 (Number type)

  var outOfBoundsInt = row[1].value
  typeof outOfBoundsInt === Spanner.Int
  console.log(outOfBoundsInt.value)
  // '--out-of-bounds-integer--' (String)
  console.log(outOfBoundsInt)
  // throws 'Integer '--out-of-bounds-integer-as-string--' is out of bounds.'
})

@lukesneeringer how should we determine if the precision is worth the complexity?

[stephenplusplus]

This issue was moved to googleapis/nodejs-bigquery#6