SQLでデータが作られた時間のタイムスタンプを作ろう for BigQuery

はじめに

今回は、BigQueryを使用してデータが作成された時間のタイムスタンプを日本時間で記録する方法について説明します。また、タイムゾーンを指定しない場合のデフォルト動作についても解説します。

サンプルデータセットの作成

まず、サンプルデータセットを作成します。データセットのパスは data-sandbox-421715.db_test_001 を使用します。

以下のクエリを実行して、サンプルデータセットを作成します。
IF NOT EXISTSでスキーマが存在しない場合のみの作成を指定できます。

-- データセットの作成
CREATE SCHEMA IF NOT EXISTS `data-sandbox-421715.db_test_001`;

-- テーブルの作成
CREATE TABLE `data-sandbox-421715.db_test_001.sample_table` (
    id INT64,
    name STRING,
    created_at TIMESTAMP
);

日本時間のタイムスタンプを使用したデータ挿入

次に、データが作成された時間のタイムスタンプを日本時間で挿入します。ここでは、CURRENT_TIMESTAMP をUTCで取得し、TIMESTAMP_ADD関数を使用して日本時間に変換して挿入します。

TIMESTAMP_ADDは、指定されたタイムスタンプに一定の時間を加えるための関数です。INTERVAL 9 HOURは、UTCタイムスタンプに9時間を加えることで日本時間に変換することを意味します。

-- データの挿入（日本時間）
INSERT INTO `data-sandbox-421715.db_test_001.sample_table` (id, name, created_at)
VALUES 
    (1, 'Alice', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 9 HOUR)),
    (2, 'Bob', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 9 HOUR)),
    (3, 'Charlie', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 9 HOUR));

また日付のみを追加したい場合は CURRENT_DATE() 関数を使用して、データ型にDATE型を指定することで日付が追加されます。

CREATE TABLE `data-sandbox-421715.db_test_001.sample_table` (
    id INT64,
    name STRING,
    created_date DATE
);
INSERT INTO `data-sandbox-421715.db_test_001.sample_table` (id, name, created_date)
VALUES 
    (1, 'Alice', CURRENT_DATE()),
    (2, 'Bob', CURRENT_DATE()),
    (3, 'Charlie', CURRENT_DATE());

タイムゾーンを指定しない場合のデフォルト動作

タイムゾーンを指定しない場合、CURRENT_TIMESTAMP はUTC（協定世界時）でタイムスタンプを返します。これは、BigQueryがデフォルトでUTCを使用するためです。

以下は、タイムゾーンを指定しないでデータを挿入する例です。

-- データの挿入（デフォルトUTC）
INSERT INTO `data-sandbox-421715.db_test_001.sample_table` (id, name, created_at)
VALUES 
    (4, 'David', CURRENT_TIMESTAMP()),
    (5, 'Eve', CURRENT_TIMESTAMP()),
    (6, 'Frank', CURRENT_TIMESTAMP());

データの確認

挿入されたデータが日本時間およびUTCで正しく記録されているか確認するためのクエリを実行します。

SELECT 
    id,
    name,
    created_at
FROM 
    `data-sandbox-421715.db_test_001.sample_table`;

このクエリを実行すると、各レコードの created_at 列に日本時間およびUTCで記録されたタイムスタンプが表示されます。

日付のみの場合

まとめ

この記事では、BigQueryを使用してデータが作成された時間のタイムスタンプを日本時間で記録する方法と、タイムゾーンを指定しない場合のデフォルト動作について紹介しました。サンプルデータを作成し、CURRENT_TIMESTAMP 関数と TIMESTAMP_ADD 関数を使用してタイムスタンプを日本時間に変換して挿入する手順を示しました。また、デフォルトでUTCを使用する場合の挿入方法についても説明しました。
これにより、データが作成された時間を日本時間およびUTCで簡単に追跡できるようになります。ぜひ試してみてください。