【Snowflake】Snowflake ~ 日時関連 ~

■ はじめに

Snowflake の 日時(日付、時刻含む)について
少しづつだが、まとめておく。

目次

【1】日時(日付、時刻含む)のデータ型
【2】現在日時を返す関数
 1)CURRENT_TIMESTAMP (CURRENT_DATE/CURRENT_TIME)
 2)SYSDATE
【3】日時(日付、時刻含む)の変換
 1)キャスト
 2)DATE_FROM_PARTS関数
【4】日時の計算
 1)DATEADD関数
 2)DATEDIFF関数
 3)DATE_TRUNC関数
 4)DAYNAME関数
 5)DATE_PART関数

【1】日時(日付、時刻含む)のデータ型

https://docs.snowflake.com/ja/sql-reference/data-types-datetime.html

より抜粋
~~~~~~~~
1)DATE
2)DATETIME
3)TIME
4)TIMESTAMP
5)TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ
~~~~~~~~
 => 詳細は、以下の関連記事を参照のこと

Snowflake ~ 基本編 / データ型 ~
https://dk521123.hatenablog.com/entry/2021/12/16/095524

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

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

2)SYSDATE

* UTC タイムゾーンの現在のタイムスタンプを返す

select sysdate();

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

SYSDATE vs CURRENT_TIMESTAMP
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 |
+--------------------------+--------------------------+

【3】日時(日付、時刻含む)の変換

1)キャスト

[1] CAST
[2] :: 演算子
[3] TO_ データ型
[4] TRY_CAST
[5] TRY_TO_ データ型

 => 詳細は、以下の関連記事を参照のこと

Snowflake ~ 基本編 / キャスト ~
https://dk521123.hatenablog.com/entry/2021/12/15/162658

[1] CAST

select cast('05-Mar-2014' as timestamp);

[2] :: 演算子

select '05-Mar-2014'::timestamp;

[3] TO_ データ型

-- TO_DATEの場合
select to_date('2013-05-17'), date('2013-05-17');

select to_date('2012.07.23', 'YYYY.MM.DD'), date('2012.07.23', 'YYYY.MM.DD');

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

[4] TRY_CAST

-- 変換できた場合は、そのままキャスト
select try_cast('05-Mar-2016' as timestamp);

+--------------------------------------+
| TRY_CAST('05-MAR-2016' AS TIMESTAMP) |
|--------------------------------------|
| 2016-03-05 00:00:00.000              |
+--------------------------------------+

-- 変換できなかった場合は、null を返却
select try_cast('05/16' as timestamp);

+--------------------------------+
| TRY_CAST('05/16' AS TIMESTAMP) |
|--------------------------------|
| NULL                           |
+--------------------------------+

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

[5] TRY_TO_ データ型

select try_to_date('2018-09-15'), try_to_date('Invalid');

+---------------------------+------------------------+
| TRY_TO_DATE('2018-09-15') | TRY_TO_DATE('INVALID') |
|---------------------------+------------------------|
| 2018-09-15                | NULL                   |
+---------------------------+------------------------+

https://docs.snowflake.com/ja/sql-reference/functions/try_to_date.html
https://docs.snowflake.com/ja/sql-reference/functions/try_to_time.html
https://docs.snowflake.com/ja/sql-reference/functions/try_to_timestamp.html

2)DATE_FROM_PARTS関数

* 年、月、月の各数値から日付を作成

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

-- DATE_FROM_PARTS( <year>, <month>, <day> )
select date_from_parts(1977, 8, 7);

+-----------------------------+
| DATE_FROM_PARTS(1977, 8, 7) |
|-----------------------------|
| 1977-08-07                  |
+-----------------------------+

【4】日時の計算

1)DATEADD関数

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

select to_date('2013-05-08') as v1, dateadd(year, 2, to_date('2013-05-08')) as v;

-- 2013-05-08 + 2 year = 2015-05-08
+------------+------------+
| V1         | V          |
|------------+------------|
| 2013-05-08 | 2015-05-08 |
+------------+------------+

構文

DATEADD( <date_or_time_part>, <value>, <date_or_time_expr> )

2)DATEDIFF関数

* 2つの日付/時刻/タイムスタンプの差を計算

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

select datediff(year, '2010-04-09 14:39:20'::timestamp, 
                      '2013-05-08 23:39:20'::timestamp) 
               as diff_years;

+------------+
| DIFF_YEARS |
|------------|
|          3 |
+------------+

3)DATE_TRUNC関数

* DATE/TIME/TIMESTAMP を指定された精度に切り捨てる

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

select to_date('2015-05-08T23:39:20.123-07:00') as "DATE1",
       date_trunc('YEAR', "DATE1") as "TRUNCATED TO YEAR",
       date_trunc('MONTH', "DATE1") as "TRUNCATED TO MONTH",
       date_trunc('DAY', "DATE1") as "TRUNCATED TO DAY";

+------------+-------------------+--------------------+------------------+
| DATE1      | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |
|------------+-------------------+--------------------+------------------|
| 2015-05-08 | 2015-01-01        | 2015-05-01         | 2015-05-08       |
+------------+-------------------+--------------------+------------------+

4)DAYNAME関数

* 曜日を算出

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

select dayname(to_date('2015-05-01')) as day;
+-----+
| DAY |
|-----|
| Fri |
+-----+

5)DATE_PART関数

* 日付/時刻/タイムスタンプから指定された各パーツ(year etc)を抽出

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

select to_timestamp('2013-05-08T23:39:20.123-07:00') as "TIME_STAMP1",
         date_part(year, "TIME_STAMP1") as "EXTRACTED YEAR";

-- yearを抽出 => 2013
+-------------------------+----------------+
| TIME_STAMP1             | EXTRACTED YEAR |
|-------------------------+----------------|
| 2013-05-08 23:39:20.123 |           2013 |
+-------------------------+----------------+

関連記事

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