Databricksの日付時刻型

投稿日: 投稿者:

デフォルトのタイムゾーン

  • DatabricksのTIMESTAMP型はUTC（協定世界時）がデフォルトで使用されます。データの挿入時や表示時も、特に指定がない限りUTCで処理されます
  • 入力タイムスタンプ文字列は、指定されたタイムゾーンのローカル タイムスタンプとして解釈されます。入力文字列でタイムゾーンが省略されている場合は、セッション タイムゾーンのローカル タイムスタンプとして解釈されます
  • デフォルトのセッションタイムゾーンはUTCです
%sql
select current_timezone()
​
current_timezone()
Etc/UTC

  • 以下はUTCとして表示されます（実行時の日本時間は2024-11-08 18:37:52.971です）

%sql
select CURRENT_TIMESTAMP()
​
CURRENT_TIMESTAMP()
2024-11-08T09:37:52.971+00:00

  • タイムゾーンの指定がある場合、指定されたタイムゾーンからUTC（セッションタイムゾーン）として変換されます

%sql
select timestamp '2024-11-08 18:37:52.971 Asia/Tokyo';
​
TIMESTAMP'2024-11-08 18:37:52.971 Asia/Tokyo'
2024-11-08T09:37:52.971+00:00%python

  • タイムゾーンの指定がない場合、指定された日付時刻をUTC（セッションタイムゾーン）として解釈します

select timestamp '2024-11-08 18:37:52.971';
​
TIMESTAMP'2024-11-08 18:37:52.971'
2024-11-08T18:37:52.971+00:00

タイムゾーンの設定

  • DatabricksはSparkを基盤としており、クエリやセッションごとにタイムゾーンを設定可能です。タイムゾーンの設定はSQLまたはPySparkコードで行えます。
  • これにより、クエリで返されるタイムスタンプデータが指定したタイムゾーンに変換されますが、データ自体はUTCで保存されていることに注意が必要です
SET spark.sql.session.timeZone = 'Asia/Tokyo';

文字列から日付時刻形式変換する

  • DatabricksではISO 8601形式（例：2024-11-08T18:37:52.971Z）やyyyy-MM-dd HH:mm:ss.SSS形式など、さまざまな形式のタイムスタンプを扱えます。文字列からタイムスタンプ型へ変換する場合は、to_timestamp()関数を使用できます
SELECT to_timestamp('2024-11-08 18:37:52.971', 'yyyy-MM-dd HH:mm:ss.SSS');

  • 日付時刻文字列が対応した形式ならフォーマット指定は省略が可能です

SELECT to_timestamp('2024-11-08T18:37:52.971Z');

タイムゾーンを考慮した変換

  • 他のタイムゾーンで保存された日時データを扱う場合、from_utc_timestampやto_utc_timestamp関数を利用してUTCとの変換が可能です
SELECT from_utc_timestamp('2024-11-08 09:37:52.971', 'Asia/Tokyo');

from_utc_timestamp('2024-11-08 09:37:52.971','Asia/Tokyo')
2024-11-08T18:37:52.971+00:00
SELECT to_utc_timestamp('2024-11-08 18:37:52.971', 'Asia/Tokyo');

to_utc_timestamp('2024-11-08 18:37:52.971','Asia/Tokyo')
2024-11-08T09:37:52.971+00:00
  • ローカルタイムスタンプをUTCとして保管する場合、to_utc_timestamp関数で変換します
%python

from pyspark.sql.functions import col, to_timestamp, to_utc_timestamp, to_date

# 文字列の日付時刻にタイムゾーンが指定されていない場合は、そのままUTCとして解釈されます
df = spark.createDataFrame([('2024-11-08 18:37:52.971',)], ['str_timestamp'])

# そのまま日付時刻形式（TIMESTAMP型）として保存すると内部ではUTCとして保存される
df = df.withColumn('jst_timestamp', to_timestamp(col('str_timestamp')))

# UTCとして保存したい場合は、日本時間をUTCに変換して保存する
# （to_timestamp で文字列を timestamp 型に変換し、その後 JST を UTC に変換）
df = df.withColumn('utc_timestamp', to_utc_timestamp(to_timestamp(col('str_timestamp')), 'Asia/Tokyo'))

df.show(truncate=False)
df.printSchema()

+-----------------------+-----------------------+-----------------------+
|str_timestamp          |jst_timestamp          |utc_timestamp          |
+-----------------------+-----------------------+-----------------------+
|2024-11-08 18:37:52.971|2024-11-08 18:37:52.971|2024-11-08 09:37:52.971|
+-----------------------+-----------------------+-----------------------+

root
 |-- str_timestamp: string (nullable = true)
 |-- jst_timestamp: timestamp (nullable = true)
 |-- utc_timestamp: timestamp (nullable = true)

日付時刻の運用方法

  • UTCで保存し、表示時にタイムゾーンを考慮する方法が一般的です。UTCはどの地域でも基準となるため、データの一貫性が保たれます。利用時にローカルタイムに変換すれば、誤差がなく、利用者にとってもわかりやすいデータとなります例えば、データをクエリで取得する際にfrom_utc_timestamp()を利用してタイムゾーンを指定することで、ローカルタイムとして表示することができます
  • タイムゾーンの概念を考慮しないシステムで、すべての日時をローカルタイムで保存・表示する場合は、タイムゾーンを考慮しない「ローカルタイムのみの環境」を維持できます。ただし、データが外部システムと共有される、異なるタイムゾーンのユーザーがアクセスするなどの要件が発生した場合、問題が生じる可能性があるため、注意が必要です
  • ローカルタイムのみの利用でシンプルな環境が求められる場合、ローカルタイムでの運用も可能ですが、一般的にはUTCで統一して保存し、利用時にローカルタイムに変換する運用が推奨されます。この方法は、データの一貫性を保ち、将来的な拡張や他のシステムとの統合にも対応しやすいため、推奨される方法となります

公式ドキュメントより

  • ANSI SQL および Spark SQL タイムスタンプ
    • Spark は、ANSI SQL で定義されている TIMESTAMP WITH TIMEZONE データ型をサポートしていません。タイムゾーン間でタイムスタンプを変換する関数がいくつかありますが、タイムゾーンの情報は保存されません。タイムスタンプに関する Databricks ドキュメントでは、次のように説明されています

Spark SQL は、タイムスタンプ タイプを TIMESTAMP WITH SESSION TIME ZONE として定義します。これは、フィールド (YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、SESSION TZ) の組み合わせであり、YEAR から SECOND フィールドは UTC タイム ゾーンの時刻を識別し、SESSION TZ は SQL 構成 spark.sql.session.timeZone から取得されます。