Bigquery での日次関数まとめ。

SQL書く際に、検索する手間を省くために、まとめています。

CURRENT_DATE. 現在の日次・タイムスタンプを取得。

CURRENT_DATE('Asia/Tokyo') as date,
FORMAT_TIMESTAMP('%Y-%m-%d',  CURRENT_TIMESTAMP, 'Asia/Tokyo') timestamp_date

結果：

EXTRACT. 年,月,日,を分けて抽出。

EXTRACTを使います。
EXTRACT(part, from 日付)

part:
DAY : 日にち
MONTH:月
DAYOFWEEK:曜日を示す 1 から 7 の範囲の整数を戻します。ここで 1 は 日曜日で 7 は土曜日です

SELECT
 date,
 EXTRACT(YEAR from date) year,     -- 年
 EXTRACT(MONTH from date) month,   -- 月
 EXTRACT(DAY from date) day,       -- 日
 EXTRACT(DAYOFWEEK from date) dayofweek, -- 曜日
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as date
 )

DATE. 日付を取得

select date(2021,10,13)

#結果
2021-10-13

DATE_ADD 日付を加算。

DATE_ADD(日付, INTERVAL 1 part)

part:
DAY
MONTH
YEAR
WEEK

SELECT
 DATE_ADD(DATE, INTERVAL 1 DAY) as day
,DATE
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as DATE
 )


#結果
day: 2021-10-14
DATE:2021-10-13

DATE_SUB. 日付を減算。

DATE_ADDの逆の考え方

SELECT
 DATE_SUB(DATE, INTERVAL 1 DAY) as day
 ,DATE
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as DATE
 )

#結果 
day: 2021-10-12.     --１引かれた状態の日付
DATE:2021-10-13

現在の日付から1年前を取得する。

SELECT
 DATE_SUB(DATE, INTERVAL 2 YEAR)  as yaer
 ,DATE
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as DATE
 )

DATE_TRUNC　日付を切り詰める

DATE_TRUNC(日付, part)

指定した日付をpartの粒度に切り詰める。
part:DAY WEEK MONTH YAER

#2021-10-13 を月まで切り詰める。

SELECT
 DATE_TRUNC(DATE,MONTH) as DAY
 ,DATE
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as DATE
 )
#結果
DAY   2021-10-01
DATE 2021-10-13

#　年まで切り詰める。
SELECT
 DATE_TRUNC(DATE,YEAR) as DAY
 ,DATE
FROM
 (
   SELECT date(CURRENT_TIMESTAMP, 'Asia/Tokyo') as DATE
 )
#結果
DAY   2021-01-01
DATE 2021-10-13

テスト用のサンプル

with test as (
SELECT d 
FROM 
UNNEST(GENERATE_DATE_ARRAY('2020-01-01','2020-03-31', INTERVAL 1 DAY)) AS d)


SELECT 
d
,date_trunc(d,month)as  month
from test
#結果

id	d	              month
1	2020-01-01	2020-01-01
2	2020-01-02	2020-01-01
3	2020-01-03	2020-01-01
4	2020-01-04	2020-01-01
5	2020-01-05	2020-01-01
6	2020-01-06	2020-01-01
7	2020-01-07	2020-01-01
8	2020-01-08	2020-01-01

DATE_DIFF(日付2,日付2,part). ふたつの日付の差をとる。

with test as (
SELECT d FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01','2020-03-31', INTERVAL 1 DAY)) AS d)
,test2 as (
select d,
date_trunc(d,month)as  month
from test
)

SELECT 
d
,month
,DATE_DIFF(d, month, day) diff
from test2


#結果
id	d	           month      diff
1	2020-01-01	2020-01-01	0
2	2020-01-02	2020-01-01	1
3	2020-01-03	2020-01-01	2
4	2020-01-04	2020-01-01	3
5	2020-01-05	2020-01-01	4
6	2020-01-06	2020-01-01	5
7	2020-01-07	2020-01-01	6
8	2020-01-08	2020-01-01	7