【Snowflake】Snowflake ～ 日時関連 / 日時取得関数 ～

Snowflake

■ はじめに

https://dk521123.hatenablog.com/entry/2022/06/17/113003

の続き。

上記で、Snowflakeに 日時について扱ったが、
current_date() / sysdate() で結構、色々ハマったので、
復習も兼ねて情報を整理する。

目次

【１】現在日時を返す関数
　１）CURRENT_TIMESTAMP (CURRENT_DATE/CURRENT_TIME)
　２）SYSDATE
【２】日時関数の違い
　１）current_timestamp() と sysdate() の違い
　２）current_date() と sysdate() の違い
【３】 current_date() を sysdate() に置き換えた際のトラブル
　１）デフォルト値での設定は文法エラーになる
　２）Joinの条件部分を変更したら1件も表示されなくなった

【１】現在日時を返す関数

１）CURRENT_TIMESTAMP (CURRENT_DATE/CURRENT_TIME)

* ローカルタイムゾーンの現在の日時(日付/時間)を返す
 => UTC時間じゃなく、あくまでローカル時間

https://docs.snowflake.com/ja/sql-reference/functions/current_timestamp.html
https://docs.snowflake.com/ja/sql-reference/functions/current_date.html
https://docs.snowflake.com/ja/sql-reference/functions/current_time.html

２）SYSDATE

* UTC タイムゾーンの現在のタイムスタンプを返す
 => ！注意！あくまで、Timestamp型（date型じゃない）

例 

select sysdate();

+--------------------------+
| SYSDATE()                |
|--------------------------+
| 2022-06-17 08:00:00.949  |
+--------------------------+

【２】日時関数の違い

１）current_timestamp() と sysdate() の違い

https://docs.snowflake.com/ja/sql-reference/functions/sysdate.html

-- より抜粋

alter session set timestamp_ntz_output_format = 'YYYY-MM-DD HH24:MI:SS.FF4';
alter session set timestamp_ltz_output_format = 'YYYY-MM-DD HH24:MI:SS.FF4';

-- Timezone を US の Los_Angeles に設定
alter session set timezone = 'America/Los_Angeles';

-- 比較実験
select sysdate(), current_timestamp();

-- 時間「08:00:00」と「00:00:00」に注目
-- UTCとローカル（今回の場合「America/Los_Angeles」）との違い
+--------------------------+--------------------------+
| SYSDATE()                | CURRENT_TIMESTAMP()      |
|--------------------------+--------------------------|
| 2019-12-23 08:00:00.1230 | 2019-12-23 00:00:00.1230 |
+--------------------------+--------------------------+

２）current_date() と sysdate() の違い

項目 current_date sysdate Memo
Time zone Local time UTC 詳しくは上述の説明参照
型 Date time(YYYY-MM-DD hh:mm:ss.xxx. e.g. '2022-09-01 08:12:22.123') Date(YYYY-MM-DD. e.g. '2022-09-01') date型の項目とsysdate()をJoinしようとしてバグだしそうになった...（後述の【３】参照）

【３】 current_date() を sysdate() に置き換えた際のトラブル

* 既存の処理で current_date() を sysdate() に置き換えた際の
　ちょっとしたトラブルをあげておく

 => 原因は、いずれも、データ型が異なり（date型とtimestamp型）
　　解決案は、いずれも、キャストした

 => １）は文法エラーですぐに気が付けたが
　　２）はエラーなく、かつ、意図しない動きをするので

 => こんなことなら「alter session set timezone = 'UTC';」
　　で済ませば楽だった、、、

１）デフォルト値での設定は文法エラーになる

* 以下のような場合は、自動的にキャストされずに
　「SQL compilation error」になる

エラーになる例 

CREATE TABLE sample_table if not exists (
  id VARCHAR,
  name VARCHAR,
  birth_date DATE DEFAULT SYSDATE() -- ★ここでエラー★
);

エラーメッセージ 

SQL compilation error:
Default value date type does not match data type for column BIRTH_DATE

解決案 

CREATE TABLE sample_table if not exists (
  id VARCHAR,
  name VARCHAR,
  birth_date DATE DEFAULT SYSDATE()::DATE -- ★キャストした★
);

２）Joinの条件部分を変更したら1件も表示されなくなった

　Joinの条件「t2.birth_date = current_date()」とした部分を
「t2.birth_date = sysdate()」に変更したら、
データが1件も表示されなくなった

イメージ 

select
  t1.id,
  t1.name
  t2.
from
  table1 as t1
inner join
  table2 as t2
on
  t2.birth_date = current_date() -- sysdate() に変更したらデータ0件
;

解決案 

select
  t1.id,
  t1.name
  t2.
from
  table1 as t1
inner join
  table2 as t2
on
  t2.birth_date = sysdate()::date -- ★キャストした★
;

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / データ型 ～
https://dk521123.hatenablog.com/entry/2021/12/16/095524
Snowflake ～ 基本編 / キャスト ～
https://dk521123.hatenablog.com/entry/2021/12/15/162658
Snowflake ～ 日時関連 ～
https://dk521123.hatenablog.com/entry/2022/06/17/113003
権限トラブル時のTips
https://dk521123.hatenablog.com/entry/2022/08/19/131922