TIMESTAMPTZ

説明

TIMESTAMPTZは、Dorisでタイムゾーン情報を持つ日付と時刻の情報を格納するために使用されるデータ型で、標準SQLのTIMESTAMP WITH TIME ZONEに対応しています。

異なるデータベースシステムでは、タイムゾーン対応のタイムスタンプ型には様々な命名規則があります：

• PostgreSQLとOracleはTIMESTAMP WITH TIME ZONEを使用
• SQL サーバーはDATETIMEOFFSETを使用
• その他の一部のデータベースはTIMESTAMP WITH LOCAL TIME ZONEを使用

SQL標準によると、単独のTIMESTAMP型はタイムゾーン情報を持つべきではありません（TIMESTAMP WITHOUT TIME ZONEに相当）。ユーザーの習慣と使いやすさを考慮して、Dorisはタイムゾーン付きタイムスタンプ型を表すために、より簡潔な名前であるTIMESTAMPTZを選択しました。現在Dorisには独立したTIMESTAMP型がないことに注意してください。ユーザーは時刻情報の格納にDATETIMEを効果的に使用できます。

TIMESTAMPTZの範囲はDATETIMEと同じで、[0000-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]です。 TIMESTAMPTZはTIMESTAMPTZ(p)形式での精度指定をサポートしており、pは精度を表し、[0, 6]の範囲で指定できます。デフォルト値は0です。つまり、TIMESTAMPTZはTIMESTAMPTZ(0)と同等です。デフォルトの出力形式は'yyyy-MM-dd HH:mm:ss.SSSSSS +XX:XX'で、+XX:XXはタイムゾーンオフセットを表します（SSSSSSの桁数は精度pによって決定されることに注意してください）。

TIMESTAMPTZ型は主キー、パーティションカラム、バケットカラムとして使用できます。

動作原理

TIMESTAMPTZの実装では、各行のデータにタイムゾーン情報を格納せず、代わりに以下のメカニズムを採用しています：

1. 格納時：すべての入力時刻値をUTC（協定世界時）に変換します。
2. クエリ時：セッションのタイムゾーン設定（time_zone変数で指定）に基づいて、UTC時刻を自動的に対応するタイムゾーンに変換して表示します。

そのため、TIMESTAMPTZはタイムゾーン変換機能付きのDATETIME型として理解でき、Dorisが内部でタイムゾーン変換を自動的に処理します。

タイムゾーン処理ルール

• 入力文字列にタイムゾーン情報が含まれている場合（例："2020-01-01 00:00:00 +03:00"）、Dorisはそのタイムゾーン情報を変換に使用します。
• 入力文字列にタイムゾーン情報が含まれていない場合（例："2020-01-01 00:00:00"）、Dorisは現在のセッションのタイムゾーン設定を変換に使用します。

ストレージと使用方法

Dorisでは、TIMESTAMPTZ型のフィールドは8バイトのストレージ領域を占有します。

TIMESTAMPTZとDATETIME型は相互変換をサポートし、変換時に適切なタイムゾーン調整が行われます。TIMESTAMPTZはDATETIMEへの暗黙的変換をサポートしており、TIMESTAMPTZを直接サポートしない関数でもこの型のデータを処理できます。

例

-- Using the current time zone (assuming +08:00) to convert a time string without time zone information
select cast("2020-01-01 00:00:00" as timestamptz);

+--------------------------------------------+
| cast("2020-01-01 00:00:00" as timestamptz) |
+--------------------------------------------+
| 2020-01-01 00:00:00 +08:00                 |
+--------------------------------------------+

-- TIMESTAMPTZ supports microseconds
select cast("2020-01-01 00:00:00.123456" as timestamptz(5));

+------------------------------------------------------+
| cast("2020-01-01 00:00:00.123456" as timestamptz(5)) |
+------------------------------------------------------+
| 2020-01-01 00:00:00.12345 +08:00                     |
+------------------------------------------------------+

-- Using a time string with time zone information
select cast("2020-01-01 00:00:00 +03:00" as timestamptz);

+---------------------------------------------------+
| cast("2020-01-01 00:00:00 +03:00" as timestamptz) |
+---------------------------------------------------+
| 2020-01-01 05:00:00 +08:00                        |
+---------------------------------------------------+

-- Converting TIMESTAMPTZ to DATETIME (with time zone conversion based on current time zone)
select cast(cast("2020-01-01 00:00:00 +03:00" as timestamptz) as datetime);

+---------------------------------------------------------------------+
| cast(cast("2020-01-01 00:00:00 +03:00" as timestamptz) as datetime) |
+---------------------------------------------------------------------+
| 2020-01-01 05:00:00                                                 |
+---------------------------------------------------------------------+

-- Converting DATETIME to TIMESTAMPTZ
select cast(cast('2023-01-02 01:00:00' as datetime) as timestamptz);

+--------------------------------------------------------------+
| cast(cast('2023-01-02 01:00:00' as datetime) as timestamptz) |
+--------------------------------------------------------------+
| 2023-01-02 01:00:00 +08:00                                   |
+--------------------------------------------------------------+

-- Using TIMESTAMPTZ in functions
select HOUR(cast("2020-01-01 00:00:00 +03:00" as timestamptz));

+---------------------------------------------------------+
| HOUR(cast("2020-01-01 00:00:00 +03:00" as timestamptz)) |
+---------------------------------------------------------+
|                                                       5 |
+---------------------------------------------------------+