【Snowflake】Snowflake ～ 日時関連 ～

Snowflake

■ はじめに

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

目次

【１】日時（日付、時刻含む）のデータ型
【２】現在日時を返す関数
【３】日時（日付、時刻含む）の変換
　１）キャスト
　２）DATE_FROM_PARTS関数
【４】日時の計算
　１）DATEADD関数
　２）DATEDIFF関数
　３）DATE_TRUNC関数
　４）DAYNAME関数
　５）DATE_PART関数 / TO_VARCHAR関数
【５】日時関連のその他の技術事項
　１）間隔定数 - INTERVAL キーワード
　２）変換
　３）生年月日（生年） <=> 年齢

【１】日時（日付、時刻含む）のデータ型

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

より抜粋
~~~~~~~~
１）DATE
２）DATETIME
３）TIME
４）TIMESTAMP
５）TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ
~~~~~~~~
 => 詳細は、以下の関連記事を参照のこと

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

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

* sysdate() や current_timestamp() などがある
* 詳細は、以下の関連記事を参照のこと。

Snowflake ～ 日時関連 / 日時取得関数 ～
https://dk521123.hatenablog.com/entry/2022/09/02/092144

【３】日時（日付、時刻含む）の変換

１）キャスト

[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('20120723', 'YYYYMMDD');

select to_date('2012/07/23', 'YYYY/MM/DD');

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

２）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                  |
+-----------------------------+

【４】日時の計算

１）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> )

補足：関数以外での加減算 

* 「【５】日時関連のその他の技術事項」の
　「２）間隔定数 - INTERVAL キーワード」を参照。

２）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 |
+------------+

３）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       |
+------------+-------------------+--------------------+------------------+

４）DAYNAME関数

* 曜日を算出

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

例 

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

５）DATE_PART関数 / TO_VARCHAR関数

* 日時のパース処理

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 |
+-------------------------+----------------+

TO_VARCHAR 

文字列型に変換する関数だが、この関数を使っても
日付のパーツをばらすことができる
 => 「DATE_PART関数」との違いは、文字列として返す点
　　（以下の例を参照のこと）

例 

select
  date('2022-01-03') as target_date,
  to_varchar(target_date, 'yyyy'), -- 2022
  to_varchar(target_date, 'MM'), -- 01
  to_varchar(target_date, 'dd') -- 03
;

-- 比較：date_partとの違い
-- 数字で出すので、01だった場合は、1として出力される
select
  date('2022-01-03') as target_date,
  date_part(year, target_date), -- 2022
  date_part(month, target_date), -- 1
  date_part(day, target_date) -- 3
;

【５】日時関連のその他の技術事項

１）間隔定数 - INTERVAL キーワード

* 簡単に日時を加減算することができる。

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

-- 現在日時から＋8時間
select current_timestamp + interval '8 hours' as interval1;

構文 

{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'

２）変換

日時の部分取得 

-- 2023
SELECT to_varchar(current_date(), 'yyyy');

-- 変数で取得
let year := to_varchar(current_date(), 'yyyy');
let month := to_varchar(current_date(), 'MM');
let day := to_varchar(current_date(), 'dd');

日付をINTで取得 

-- 20230809
SELECT to_char(current_date(), 'YYYYMMDD')::int;

３）生年月日（生年） <=> 年齢

[1] 生年月日 => 年齢
https://qiita.com/r_1204/items/872f21e0fb13b22e650a

-- 1975-10-01 の場合
select
case when dateadd(year, datediff(years, '1975-10-01', current_date), '1975-10-01') > current_date
   then datediff(years, '1975-10-01', current_date) -1
   else datediff(years, '1975-10-01', current_date)
end as age;

[2] 年齢 => 生年 

-- 35 歳（で、「YYYY-01-01 00:00:00」は固定）
select
    (YEAR(current_date) - 35) || '-01-01 00:00:00' as barth_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/09/02/092144
Snowflake ～ Timezone ～
https://dk521123.hatenablog.com/entry/2024/10/10/002436

ランキング参加中
プログラミング