Snowflakeの3種類のTIMESTAMPの違いをまとめてみた #SnowflakeDB

2022.02.07

さがらです。

SnowflakeではTIMESTAMPに関して3種類の型があります。その3種の違いを本記事でまとめてみます。

Snowflakeにおける3種類のTIMESTAMP

Snowflakeでは、以下3種類のTIMESTAMPがあります。

  • TIMESTAMP_LTZ
  • TIMESTAMP_NTZ
  • TIMESTAMP_TZ

それぞれの説明は公式Docにも載っていますが、パッと見ただけでは違いがわからないと思います。(私も最初はわかりませんでした。笑)

そこで、次章から公式Docのサンプルコードに沿って、それぞれの違いをまとめていきます。

TIMESTAMP_LTZ

まず1つ目、TIMESTAMP_LTZについてです。

簡単に言うと、timezoneセッションパラメーターの値に応じて、タイムゾーンが動的に変化するTIMESTAMPです。

まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。

create or replace table ts_test(ts timestamp_ltz);

alter session set timezone = 'America/Los_Angeles';

insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

この上で、以下のSELECT文を実行してみます。

select ts, hour(ts) from ts_test;

1行目2014-01-01 16:00:00としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」が追記されて登録されました。

2行目2014-01-02 16:00:00 +00:00としてタイムゾーンを指定してINSERTしたレコードは、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」に変換された値が登録されました。

続けて、timezoneパラメータをAmerica/New_Yorkにして、先ほどと同じSELECT文を実行してみます。

alter session set timezone = 'America/New_York';

select ts, hour(ts) from ts_test;

すると、1行目も2行目もAmerica/New_Yorkのタイムゾーン「-0500 (UTC -5h)」に変換された値が表示されました。

このように、timezoneセッションパラメーターの値に応じて値が動的に変化するのが、TIMESTAMP_LTZです。

TIMESTAMP_NTZ

2つ目、TIMESTAMP_NTZについてです。

簡単に言うと、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないTIMESTAMPです。

まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。

create or replace table ts_test(ts timestamp_ntz);

alter session set timezone = 'America/Los_Angeles';

insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

この上で、以下のSELECT文を実行してみます。

select ts, hour(ts) from ts_test;

下図がこのSELECT文の実行結果ですが、1行目はタイムゾーンを含まない値、2行目はタイムゾーンを含む値をINSERTしましたが、どちらもタイムゾーンがなくなった形で表示されているのがわかると思います。timezoneパラメータのAmerica/Los_Angelesの影響も特にありません。

このように、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないのがTIMESTAMP_NTZです。

TIMESTAMP_TZ

3つ目、TIMESTAMP_TZについてです。

簡単に言うと、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないTIMESTAMPです。

まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。

create or replace table ts_test(ts timestamp_tz);

alter session set timezone = 'America/Los_Angeles';

insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');

この上で、以下のSELECT文を実行してみます。

select ts, hour(ts) from ts_test;

下図がこのSELECT文の実行結果となります。

1行目2014-01-01 16:00:00としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」が追記されました。

2行目2014-01-02 16:00:00 +00:00としてタイムゾーンを指定してINSERTしたレコードは、INSERT時の値のタイムゾーンのまま「+0000」で登録されました。

続けて、timezoneパラメータをAmerica/New_Yorkにして、先ほどと同じSELECT文を実行してみます。

alter session set timezone = 'America/New_York';

select ts, hour(ts) from ts_test;

すると、1行目も2行目も値が変化していないことがわかります。timezoneパラメータの値を変えることで、TIMESTAMP_LTZの場合は値のタイムゾーンも変化していていたのが、TIMESTAMP_TZでは値のタイムゾーンが変化しませんでした。

このように、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないのがTIMESTAMP_TZです。

3種類のTIMESTAMPの使い分け

ここで、3種類のTIMESTAMPの使い分けについてまとめてみます。

  • TIMESTAMP_NTZ ※デフォルト値
    • タイムゾーンを特に意識せずに、日時データを保持しておきたい場合
    • ※参考:SnowflakeにはDATETIMEという型もありますが、これはTIMESTAMP_NTZのエイリアスです
  • TIMESTAMP_TZ
    • タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンごとに値を変更させたくない場合
  • TIMESTAMP_LTZ
    • タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンに合わせて値を動的に変更させたい場合

おまけ:「TIMESTAMP」型について

実は、SnowflakeではTIMESTAMPという型もあります。

なのですが、TIMESTAMPはこれまでに説明した3種類のTIMESTAMP_*のエイリアスで、デフォルトではTIMESTAMP_NTZを意味しています。

もし、TIMESTAMPで別のTIMESTAMP_*を参照させたい場合は、TIMESTAMP_TYPE_MAPPINGというセッションパラメーターをを変更すればOKです。

※対象のセッション中TIMESTAMP型を指定したときにTIMESTAMP_TZが使われるように変更するためのクエリを以下に記しておきます。

alter session set TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_TZ';

show parameters like 'TIMESTAMP_TYPE_MAPPING';

最後に

Snowflakeでの3種類のTIMESTAMPの違いをまとめてみました。

データを管理する上で日時は欠かせない要素ですので、この知識を抑えておくといざというときに困惑せずに済むはずです。