losing precision in converting TIMESTAMP and INT64 to Javascript Number

@c0b

From @c0b on October 9, 2016 5:5

googleapis/google-cloud-node#1648 (comment)

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

googleapis/google-cloud-node#1648 (comment)

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

Copied from original issue: googleapis/google-cloud-node#1681

@c0b

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

From @callmehiphop on October 10, 2016 19:55

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

@lukesneeringer

From @lukesneeringer on March 13, 2017 19:41

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.

@lukesneeringer

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?

@alexander-fenster

@alexander-fenster any input?

@stephenplusplus

A pretty neat feature that the mysql client offers is type casting. Users can essentially supply a callback to convert the raw value to whatever they want. With newer versions of Node supporting BigInt, I think it might benefit the BigQuery library (and maybe others like Spanner and Datastore) if we offered similar functionality.

@stephenplusplus @bcoe @JustinBeckwith WDYT?

@shollyman

Sounds awesome to me, but please do run by @shollyman and @tswast first :)

@callmehiphop

@callmehiphop seems like a great use of BigInt, would be interesting if you could find an elegant way to add this feature progressively so that folks can opt in to using BigInt if they need the precision.

Hello everybody,

After reading this issue, I tried to query a table with identifiers stored in INT64 columns with wrapIntegers property set to true, but it loses precision even using the latest release (5.9.3) on Node.js 14. Could you take a look at this code, please? I do not see anything wrong.

const client = new BigQuery();

const sqlQuery = `SELECT 160864210488964916`;

const queryOptions: Query = {
  query: sqlQuery,
  wrapIntegers: true,
};

const [rows] = await client.query(queryOptions);

console.log(rows);
// [ { f0_: 160864210488964930 } ]

Thank you!

@alvarowolfx

(I'm tempted to classify this as a "bug" but it is technically a feature request; asking @alvarowolfx to look into it.)

@sergioregueira

@sergioregueira, the behavior that you reported is indeed an issue in our library. Basically the Query.wrapIntegers was being ignored. For it to works you need to pass as a QueryResultsOptions.

 const client = new BigQuery();

const sqlQuery = `SELECT 160864210488964916`;

const query: Query = {
  query: sqlQuery,
};

const queryOptions: QueryResultsOptions = {
  wrapIntegers: true,
};

const [rows] = await client.query(query, queryOptions);

console.log(rows);
// [{"f0_":{"type":"BigQueryInt","value":"160864210488964916"}}]

But I opened a PR with a fix for that to support the way that you reported too. See #1191

As for the microsecond precision support, another PR was opened to fix that ( #1192 ). It basically that uses @google-cloud/precise-date to parse timestamp arriving as a float64 to a timestamp string with microsecond resolution.

Example output:

const bigquery = new BigQuery();
const [rows] = await bigquery.query({
  query: 'SELECT TIMESTAMP("2014-09-27 12:30:00.123456Z")',
});
console.log(JSON.stringify(rows));
// [{"f0_":{"value":"2014-09-27T12:30:00.123456000Z"}}]

feat: add support for microseconds precision (#1192)

Navigation Menu

Repository navigation

losing precision in converting TIMESTAMP and INT64 to Javascript Number #6

TIMESTAMP

INT64

21 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

losing precision in converting TIMESTAMP and INT64 to Javascript Number #6

Description

Activity

stephenplusplus commented on Nov 17, 2017

stephenplusplus commented on Nov 17, 2017

stephenplusplus commented on Nov 17, 2017

stephenplusplus commented on Nov 17, 2017

TIMESTAMP

INT64

stephenplusplus commented on Apr 2, 2018

callmehiphop commented on Mar 18, 2019

JustinBeckwith commented on Mar 18, 2019

bcoe commented on Mar 18, 2019

21 remaining items

sergioregueira commented on Dec 16, 2021

meredithslota commented on Mar 10, 2023

alvarowolfx commented on Mar 10, 2023

alvarowolfx commented on Mar 10, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions