feat: add support for microseconds precision (#1192)

Parses timestamps from the backend ( that arrives as a float64 ) using the [@google-cloud/precise-date](https://www.npmjs.com/package/@google-cloud/precise-date) lib to support 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"}}] ``` Fixes #6
googleapis · Mar 17, 2023 · b5801a6 · b5801a6
1 parent c8fba1a
commit b5801a6
Show file tree

Hide file tree

Showing 3 changed files with 62 additions and 7 deletions.
diff --git a/package.json b/package.json
@@ -50,6 +50,7 @@
   "dependencies": {
     "@google-cloud/common": "^4.0.0",
     "@google-cloud/paginator": "^4.0.0",
+    "@google-cloud/precise-date": "^3.0.1",
     "@google-cloud/promisify": "^3.0.0",
     "arrify": "^2.0.1",
     "big.js": "^6.0.0",

diff --git a/src/bigquery.ts b/src/bigquery.ts
@@ -23,6 +23,7 @@ import {
 } from '@google-cloud/common';
 import {paginator, ResourceStream} from '@google-cloud/paginator';
 import {promisifyAll} from '@google-cloud/promisify';
+import {PreciseDate} from '@google-cloud/precise-date';
 import arrify = require('arrify');
 import {Big} from 'big.js';
 import * as extend from 'extend';
@@ -585,7 +586,7 @@ export class BigQuery extends Service {
           break;
         }
         case 'TIMESTAMP': {
-          value = BigQuery.timestamp(new Date(value * 1000));
+          value = BigQuery.timestamp(value);
           break;
         }
         case 'GEOGRAPHY': {
@@ -844,11 +845,11 @@ export class BigQuery extends Service {
    * const timestamp = bigquery.timestamp(new Date());
    * ```
    */
-  static timestamp(value: Date | string) {
+  static timestamp(value: Date | PreciseDate | string | number) {
     return new BigQueryTimestamp(value);
   }
 
-  timestamp(value: Date | string) {
+  timestamp(value: Date | PreciseDate | string | number) {
     return BigQuery.timestamp(value);
   }
 
@@ -2125,8 +2126,42 @@ export class Geography {
  */
 export class BigQueryTimestamp {
   value: string;
-  constructor(value: Date | string) {
-    this.value = new Date(value).toJSON();
+  constructor(value: Date | PreciseDate | string | number) {
+    let pd: PreciseDate;
+    if (value instanceof PreciseDate) {
+      pd = value;
+    } else if (value instanceof Date) {
+      pd = new PreciseDate(value);
+    } else if (typeof value === 'string') {
+      if (/^\d{4}-\d{1,2}-\d{1,2}/.test(value)) {
+        pd = new PreciseDate(value);
+      } else {
+        const floatValue = Number.parseFloat(value);
+        if (!Number.isNaN(floatValue)) {
+          pd = this.fromFloatValue_(floatValue);
+        } else {
+          pd = new PreciseDate(value);
+        }
+      }
+    } else {
+      pd = this.fromFloatValue_(value);
+    }
+    // to keep backward compatibility, only converts with microsecond
+    // precision if needed.
+    if (pd.getMicroseconds() > 0) {
+      this.value = pd.toISOString();
+    } else {
+      this.value = new Date(pd.getTime()).toJSON();
+    }
+  }
+
+  fromFloatValue_(value: number): PreciseDate {
+    const secs = Math.trunc(value);
+    // Timestamps in BigQuery have microsecond precision, so we must
+    // return a round number of microseconds.
+    const micros = Math.trunc((value - secs) * 1e6 + 0.5);
+    const pd = new PreciseDate([secs, micros * 1000]);
+    return pd;
   }
 }
 

diff --git a/test/bigquery.ts b/test/bigquery.ts
@@ -42,6 +42,7 @@ import {
   TableField,
 } from '../src';
 import {SinonStub} from 'sinon';
+import {PreciseDate} from '@google-cloud/precise-date';
 
 const fakeUuid = extend(true, {}, uuid);
 
@@ -453,7 +454,7 @@ describe('BigQuery', () => {
             f: [
               {v: '3'},
               {v: 'Milo'},
-              {v: String(now.valueOf() / 1000)},
+              {v: now.valueOf() * 1000},
               {v: 'false'},
               {v: 'true'},
               {v: '5.222330009847'},
@@ -505,7 +506,7 @@ describe('BigQuery', () => {
             id: 3,
             name: 'Milo',
             dob: {
-              input: now,
+              input: now.valueOf() * 1000,
               type: 'fakeTimestamp',
             },
             has_claws: false,
@@ -803,8 +804,11 @@ describe('BigQuery', () => {
 
   describe('timestamp', () => {
     const INPUT_STRING = '2016-12-06T12:00:00.000Z';
+    const INPUT_STRING_MICROS = '2016-12-06T12:00:00.123456Z';
     const INPUT_DATE = new Date(INPUT_STRING);
+    const INPUT_PRECISE_DATE = new PreciseDate(INPUT_STRING_MICROS);
     const EXPECTED_VALUE = INPUT_DATE.toJSON();
+    const EXPECTED_VALUE_MICROS = INPUT_PRECISE_DATE.toISOString();
 
     // tslint:disable-next-line ban
     it.skip('should expose static and instance constructors', () => {
@@ -822,15 +826,30 @@ describe('BigQuery', () => {
       assert.strictEqual(timestamp.constructor.name, 'BigQueryTimestamp');
     });
 
+    it('should accept a NaN', () => {
+      const timestamp = bq.timestamp(NaN);
+      assert.strictEqual(timestamp.value, null);
+    });
+
     it('should accept a string', () => {
       const timestamp = bq.timestamp(INPUT_STRING);
       assert.strictEqual(timestamp.value, EXPECTED_VALUE);
     });
 
+    it('should accept a string with microseconds', () => {
+      const timestamp = bq.timestamp(INPUT_STRING_MICROS);
+      assert.strictEqual(timestamp.value, EXPECTED_VALUE_MICROS);
+    });
+
     it('should accept a Date object', () => {
       const timestamp = bq.timestamp(INPUT_DATE);
       assert.strictEqual(timestamp.value, EXPECTED_VALUE);
     });
+
+    it('should accept a PreciseDate object', () => {
+      const timestamp = bq.timestamp(INPUT_PRECISE_DATE);
+      assert.strictEqual(timestamp.value, EXPECTED_VALUE_MICROS);
+    });
   });
 
   describe('geography', () => {