Snowflakeの日付関数

Snowflakeの日付関数についてよく使うものを備忘録としてまとめました。

現在の日時

select current_date() as cur_d,
       cast(current_timestamp as datetime) as cur_dt,
       current_timestamp() as cur_ts,
       to_timestamp_ntz(current_timestamp()) as cur_ntz
;

+-------------+----------------------------+--------------------------------+--------------------------+
| cur_d       | cur_dt                     | cur_ts                         | cur_ntz                  |
+-------------+----------------------------+--------------------------------+--------------------------+
| 2024-09-20  | 2024-09-20 12:34:56.123    | 2024-09-20 12:34:56.123 +0900  | 2024-09-20 12:34:56.123  |
+-------------+----------------------------+--------------------------------+--------------------------+

現在の日時を YYYY-MM-DD HH:MM:SS形式の文字列で表示

select to_varchar(current_date(), 'yyyy-mm-dd hh24:mi:ss'),
       to_varchar(cast(current_timestamp as datetime), 'yyyy-mm-dd hh24:mi:ss'),
       to_varchar(current_timestamp(), 'yyyy-mm-dd hh24:mi:ss')

+---------------------+---------------------+---------------------+
| fmt_d               | fmt_dt              | fmt_ts              |
+---------------------+---------------------+---------------------+
| 2024-09-20 00:00:00 | 2024-09-20 12:34:56 | 2024-09-20 12:34:56 |
+---------------------+---------------------+---------------------+

タイムゾーンを日本にする

-- タイムゾーンの設定
alter session set timezone = 'Asia/Tokyo';

-- タイムゾーンの確認
show parameters like 'TIMEZONE';

+----------+--------------+---------------------+-----------------+
| key      | value        | default             | ・・・          |
+----------+--------------+---------------------+-----------------+
| TIMEZONE | Asia/Tokyo   | America/Los_Angeles | ・・・          |
+----------+--------------+---------------------+-----------------+

タイムスタンプを別のタイムゾーンに変換

SELECT current_timestamp() as current_ts, 
       convert_timezone('UTC', current_timestamp()) as to_utc,
       convert_timezone('America/Chicago', current_timestamp()) as to_LA,
;

+-------------------------------+-------------------------------+-------------------------------+
| current_ts                    | to_utc                        | to_LA                         |
+-------------------------------+-------------------------------+-------------------------------+
| 2024-09-21 19:00:00.000 +0900 | 2024-09-21 10:00:00.000 +0000 | 2024-09-21 05:00:00.000 -0500 |
+-------------------------------+-------------------------------+-------------------------------+

現在の日時を月や日（時分秒は全て0）で丸める

select date_trunc('MONTH', current_date()) as dt_to_month,
       date_trunc('DAY', current_timestamp()) as ts_to_day,
       date_trunc('DAY', to_timestamp_ntz(current_timestamp())) as ntz_to_day
;

+---------------+-------------------------------+--------------------------+
| dt_to_month   | ts_to_day                     | ntz_to_day               |
+---------------+-------------------------------+--------------------------+
| 2024-09-01    | 2024-09-21 00:00:00.000 +0900 | 2024-09-21 00:00:00.000  |
+---------------+-------------------------------+--------------------------+

現在の日時から 年 、 月 、 日 を抜き出す

select date_part(year, current_date) as year,
       date_part(month, current_date) as mont,
       date_part(day, current_date) as day
;

+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2024 |  09   | 20  |
+------+-------+-----+

※timestamp も同様

現在の日時に日を加算

select dateadd(year, 2, current_date) as year_add,
       dateadd(month, 2, current_date) as month_add,
       dateadd(day, 2, current_date) as day_add
;

+-------------+-------------+--------------+
| year_add    | month_add   | day_add      |
+-------------+----------------------------+
| 2026-09-20  | 2024-11-20  | 2024-09-22   |
+-------------+----------------------------+

文字列から日付型（date型、timestamp型）へ変換

select to_date('20240920', 'YYYYMMDD') as prd_d,
       to_timestamp('2024/09/20 12:34:56', 'yyyy/mm/dd hh24:mi:ss') as prd_ts,
       to_timestamp_ntz('2024-09-20 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as prs_ts_ntz
;

+-------------+--------------------------+--------------------------+
| prd_d       | parse_ts                 | prs_ts_ntz               |
+-------------+--------------------------+--------------------------+
| 2024-09-20  | 2024-09-20 12:34:56.000  | 2024-09-20 12:34:56.000  |
+-------------+--------------------------+--------------------------+