LoginSignup
2
1

More than 3 years have passed since last update.

@hotoku

JavaScriptで書くBigQueryのUDFでの日付の取り扱い方

Last updated at Posted at 2021-05-24

tl; dr

日付を扱うBigQueryのUDFをJavaScriptで書く際、BQのタイムゾーンはGMT-8（米国太平洋時間）なので、それを考慮して調整する必要がある。

具体的には引数として入ってきたオブジェクトに対して

date.setMinutes(date.getMinutes() + date.getTimezoneOffset())

を実行し、値をreturnする前に

date.setMinutes(date.getMinutes() - date.getTimezoneOffset())

する。

また、ローカルで関数をテストする際には、引数にはGMTで00:00:00になるようなDateオブジェクトを渡す。具体的には、

date.setMinutes(date.getMinutes() - date.getTimezoneOffset())

を実行する。

現象の確認

ソースコード（TypeScript）

日付型の値を受け取って、年月日の「日」だけを変更する関数を定義する。何も考えずにTypeScriptで書いてみる。

import * as dayjs from "dayjs";

interface Ret {
  orig: Date;
  set0: Date;
  set1: Date;
  set2: Date;
}

export function myfunc(date: Date): Ret {
  const orig = dayjs(date);
  const set0 = orig.set("date", 0).toDate();
  const set1 = orig.set("date", 1).toDate();
  const set2 = orig.set("date", 2).toDate();
  return {
    orig: date,
    set0: set0,
    set1: set1,
    set2: set2,
  };
}

与えられた日付から、

  • それ自身
  • 前月の最終日
  • 今月の1日
  • 今月の2日

を返す関数を定義している。これを元に、UDFを作る。

UDF定義

UDFのシグネチャは、Date型引数を取って、日付がずらされたDate型を含む構造体を返す形。👇のSQL参照。TypeScriptで書いたコードをUDFにする方法はこちらの記事参照。

#standardSQL
CREATE OR REPLACE FUNCTION `myfunc.myfunc`(date Date)
RETURNS STRUCT< orig Date, set0 Date, set1 Date, set2 Date > 
LANGUAGE js AS """
// ここに.tsをコンパイルしたjsのコードが入る
return myfunc.myfunc(date);
""";

実行してみる

こんなクエリを投げると・・

WITH
  temp1 AS (
    SELECT
      `myfunc.myfunc`("2000-3-1") AS x
  )
SELECT
  x.orig,
  x.set0,
  x.set1,
  x.set2
FROM
  temp1;

こんな結果が返ってくる。むむむ。

+------------+------------+------------+------------+
|    orig    |    set0    |    set1    |    set2    |
+------------+------------+------------+------------+
| 2000-03-01 | 2000-02-01 | 2000-02-02 | 2000-02-03 |
+------------+------------+------------+------------+
  • 前月の最終日が返ってきてほしかったが・・前月の１日
  • 当月の１日が返ってきてほしかったが・・前月の２日
  • 当月の２日が返ってきてほしかったが・・限月の３日

が返ってきている。

ローカルで実行してみる

中で何が起こってるか知りたい。まずはローカルで現象を再現したい。こんなテストコードを作って実行してみると・・

import * as main from "../src/main";

describe("test", () => {
  it("2000-03-01", () => {
    const d = new Date(2000, 2, 1); // JavaScriptの日付は、月だけ0始まり
    const ret = main.myfunc(d);
    console.log(ret);
  });
});

こんな出力が得られる。

$ npm run test

> test
> mocha -r ts-node/register test/*.ts



  test
{
  orig: 2000-02-29T15:00:00.000Z,
  set0: 2000-02-28T15:00:00.000Z,
  set1: 2000-02-29T15:00:00.000Z,
  set2: 2000-03-01T15:00:00.000Z
}
    ✓ 2000-03-01


  1 passing (8ms)

テストといいつつ、一つもassertせずに結果を表示しているだけなので見かけ上は成功しているように見えるが、期待とは異なる結果である。さらに、BQでの実行結果とも全然違う。

origが前日の15時になっているので、タイムゾーンが関係してそうな感じがする。

タイムゾーンを調べる

JavaScriptの日付オブジェクトからタイムゾーンに関する情報を得るには、getTimezoneOffsetを使う。

ソースを以下のように変更し、BQ上のUDFと、ローカルでテストを走らせたときのタイムゾーンを調べてみる。

import * as dayjs from "dayjs";

interface Ret {
  offset: number;
  orig: string;
  set0: string;
  set1: string;
  set2: string;
}

export function myfunc(date: Date): Ret {
  const offset = date.getTimezoneOffset();
  const orig = dayjs(date);
  const set0 = orig.set("date", 0);
  const set1 = orig.set("date", 1);
  const set2 = orig.set("date", 2);
  return {
    offset: offset,
    orig: orig.toISOString(),
    set0: set0.toISOString(),
    set1: set1.toISOString(),
    set2: set2.toISOString(),
  };
}

BQでの結果は、次のようになり・・

+--------+--------------------------+--------------------------+--------------------------+--------------------------+
| offset |           orig           |           set0           |           set1           |           set2           |
+--------+--------------------------+--------------------------+--------------------------+--------------------------+
|    480 | 2000-03-01T00:00:00.000Z | 2000-02-01T00:00:00.000Z | 2000-02-02T00:00:00.000Z | 2000-02-03T00:00:00.000Z |
+--------+--------------------------+--------------------------+--------------------------+--------------------------+

ローカルでの結果は、次のようになる。

npm run test

> test
> mocha -r ts-node/register test/*.ts



  test
{
  offset: -540,
  orig: '2000-02-29T15:00:00.000Z',
  set0: '2000-02-28T15:00:00.000Z',
  set1: '2000-02-29T15:00:00.000Z',
  set2: '2000-03-01T15:00:00.000Z'
}
    ✓ 2000-03-01


  1 passing (7ms)

BQ上ではGMT-8、私のマシンはGMT+9がタイムゾーンとして設定されていそう。

挙動をよく考えてみる

BQ上で、set0が、期待と違った値になってしまうのは、おそらく次のような機序であると思われる。

  • 2000-03-01T00:00:00Zは2000-02-29T16:00:00-08（つまり、GMTで2000年3月1日の0時は、米国太平洋時間の人にとっては2/29の16時）
  • 2月29日の前月の最終日は1月31日
  • 2000-01-31T16:00:00-08は2000-02-01T00:00:00Z

set1、set2についても同様に考えると、それぞれの値が説明できる。

対応を考える

ということは、GMTで0時となっているタイムスタンプを、BQが採用しているタイムゾーンで0時になるように修正してから日付をいじればうまくいきそう。タイムゾーンの情報は、getTimezoneOffsetで取れるのだった。これを使って調整してやる。👇のように関数を書き換えて・・

import * as dayjs from "dayjs";

interface Ret {
  offset: number;
  orig: string;
  set0: string;
  set1: string;
  set2: string;
}

function adjust(dates: Array<Date>, offset: number, sign: 1 | -1): void {
  dates.map((d) => {
    d.setMinutes(d.getMinutes() + offset * sign);
  });
}

export function myfunc(date: Date): Ret {
  const offset = date.getTimezoneOffset();
  adjust([date], offset, 1);
  const orig = dayjs(date);
  const set0 = orig.set("date", 0).toDate();
  const set1 = orig.set("date", 1).toDate();
  const set2 = orig.set("date", 2).toDate();
  adjust([date, set0, set1, set2], offset, -1);
  return {
    offset: offset,
    orig: date.toISOString(),
    set0: set0.toISOString(),
    set1: set1.toISOString(),
    set2: set2.toISOString(),
  };
}

先ほどと同じSQLを実行すると、以下のような結果が返ってくる。

+--------+--------------------------+--------------------------+--------------------------+--------------------------+
| offset |           orig           |           set0           |           set1           |           set2           |
+--------+--------------------------+--------------------------+--------------------------+--------------------------+
|    480 | 2000-03-01T00:00:00.000Z | 2000-02-29T00:00:00.000Z | 2000-03-01T00:00:00.000Z | 2000-03-02T00:00:00.000Z |
+--------+--------------------------+--------------------------+--------------------------+--------------------------+

意図通りの値が得られていることが分かる。

テスト用の調整

しかし、ローカルでテストを実行すると、以下のような結果となる。

npm run test

> test
> mocha -r ts-node/register test/*.ts



  test
{
  offset: -540,
  orig: '2000-02-29T15:00:00.000Z',
  set0: '2000-01-31T15:00:00.000Z',
  set1: '2000-02-01T15:00:00.000Z',
  set2: '2000-02-02T15:00:00.000Z'
}
    ✓ 2000-03-01


  1 passing (8ms)

関数に渡したオブジェクトが2000-03-01T00:00:00+09 = 2020-02-29T15:00:00Zであることが原因である。ローカルでテストを実行するためには、テストコードから関数に渡す引数が、GMTで0時になるように調整する必要がある。以下のようにテストコードを変更すると

import * as main from "../src/main";

function adjust(date: Date): void {
  date.setTime(date.getTime() - date.getTimezoneOffset() * 60 * 1000);
}

describe("test", () => {
  it("2000-03-01", () => {
    const d = new Date(2000, 2, 1); // JavaScriptの日付は、月だけ0始まり
    adjust(d);
    const ret = main.myfunc(d);
    console.log(ret);
  });
});

以下のような出力が得られる。

npm run test

> test
> mocha -r ts-node/register test/*.ts



  test
{
  offset: -540,
  orig: '2000-03-01T00:00:00.000Z',
  set0: '2000-02-29T00:00:00.000Z',
  set1: '2000-03-01T00:00:00.000Z',
  set2: '2000-03-02T00:00:00.000Z'
}
    ✓ 2000-03-01


  1 passing (7ms)

期待通りの動作になっていることが分かる。

レポジトリ

この記事の検証で使ったソースコードはこちらにあります

2
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1