SQL

SQLの日付関数の使い方【BigQuery版:DATE_TRUNC・DATE_DIFF・FORMAT_DATE・タイムゾーン】

酒井 裕麻株式会社Bistro 代表取締役社長

売上の月別集計、曜日別の比較、最終購入からの経過日数──データ分析では、日付の扱いが欠かせません。ところが、日付の関数はデータベースによって書き方が違い、迷いやすいところでもあります。

この記事では、BigQueryでよく使う日付の関数を、実行結果付きで解説します。GA4のデータで必要になる、タイムスタンプを日本時間に直す方法も紹介します。

参考記事

SQLの集計関数の使い方【SUM・AVG・COUNT・MAX・MIN/GROUP BY】
SQL WITH句(CTE)の使い方【長いクエリを読みやすく組み立てる】
Rで日付データを扱う方法【lubridateで変換・曜日・月初・週単位の集計】

この記事で使うデータ

JOINの記事と同じ、架空のお店の顧客テーブル(shop.customers)・注文テーブル(shop.orders)・日別売上テーブル(shop.daily_sales)を使います。BigQueryで同じデータを作る場合は、サンプルデータのSQL(shop_sample.sql)を実行してください。

BigQueryの日付と時刻の型

型 中身 例
DATE 日付だけ 2026-03-18
DATETIME 日付と時刻(タイムゾーンなし) 2026-03-18T21:30:00
TIMESTAMP ある瞬間を表す日時(内部はUTC) 2026-03-18 12:30:00 UTC

この記事では、主に DATE 型の関数を紹介します。DATETIME や TIMESTAMP にも、DATETIME_TRUNC・TIMESTAMP_DIFF のように、同じ考え方の関数が用意されています。

年・月・日・曜日を取り出す(EXTRACT)

SELECT
  order_id
  , order_date
  , EXTRACT(YEAR FROM order_date) AS y
  , EXTRACT(MONTH FROM order_date) AS m
  , EXTRACT(DAY FROM order_date) AS d
  , EXTRACT(DAYOFWEEK FROM order_date) AS dow
  , FORMAT_DATE('%a', order_date) AS dow_name
FROM
  shop.orders
WHERE
  order_id <= 1004
ORDER BY
  order_id

実行結果

order_id order_date y m d dow dow_name
1001 2026-03-02 2026 3 2 2 Mon
1002 2026-03-03 2026 3 3 3 Tue
1003 2026-03-07 2026 3 7 7 Sat
1004 2026-03-08 2026 3 8 1 Sun

EXTRACT(DAYOFWEEK FROM …) は、日曜日が1、土曜日が7です。月曜日始まりではないので注意しましょう。

曜日別に集計する

渋谷店の曜日別の平均売上を出してみます。

SELECT
  EXTRACT(DAYOFWEEK FROM sales_date) AS dow_num
  , FORMAT_DATE('%a', sales_date) AS dow
  , COUNT(*) AS days
  , ROUND(AVG(sales)) AS avg_sales
FROM
  shop.daily_sales
WHERE
  store = '渋谷店'
GROUP BY
  dow_num
  , dow
ORDER BY
  dow_num

実行結果

dow_num dow days avg_sales
1 Sun 4 254175
2 Mon 4 170125
3 Tue 4 172775
4 Wed 4 176775
5 Thu 4 194675
6 Fri 4 190075
7 Sat 4 237375

土日の売上が、平日より大きいことがわかります。曜日の番号(dow_num)も一緒に出しておくと、曜日の順番どおりに並べられます。

月初・週初にまとめる(DATE_TRUNC)

DATE_TRUNC(日付, 単位) は、日付を月の初日や週の初日に切りそろえる関数です。月別・週別の集計に欠かせません。

SELECT
  DATE '2026-03-18' AS d
  , DATE_TRUNC(DATE '2026-03-18', MONTH) AS month_start
  , DATE_TRUNC(DATE '2026-03-18', WEEK(MONDAY)) AS week_start
  , LAST_DAY(DATE '2026-03-18', MONTH) AS month_end

実行結果

d month_start week_start month_end
2026-03-18 2026-03-01 2026-03-16 2026-03-31
書き方 結果
DATE_TRUNC(日付, MONTH) その月の1日
DATE_TRUNC(日付, WEEK) その週の日曜日
DATE_TRUNC(日付, WEEK(MONDAY)) その週の月曜日
DATE_TRUNC(日付, YEAR) その年の1月1日
LAST_DAY(日付, MONTH) その月の末日

WEEK だけだと日曜日始まりになります。日本の業務では月曜日始まりが多いので、WEEK(MONDAY) と指定するのがおすすめです。

週別に集計する

SELECT
  DATE_TRUNC(sales_date, WEEK(MONDAY)) AS week_start
  , SUM(sales) AS sales
  , COUNT(*) AS days
FROM
  shop.daily_sales
WHERE
  store = '渋谷店'
GROUP BY
  week_start
ORDER BY
  week_start

実行結果

week_start sales days
2026-02-23 244200 1
2026-03-02 1385200 7
2026-03-09 1378500 7
2026-03-16 1398000 7
2026-03-23 1178000 6

最初の週(3月1日の日曜日だけ)と最後の週(6日分)は、7日そろっていないので売上が小さくなっています。週別の推移を比べるときは、days の列を一緒に出して、そろっていない週に気づけるようにしておきましょう。

日付の足し算・引き算(DATE_ADD・DATE_SUB)

SELECT
  DATE_ADD(DATE '2026-03-18', INTERVAL 7 DAY) AS plus_7d
  , DATE_SUB(DATE '2026-03-18', INTERVAL 1 MONTH) AS minus_1m
  , DATE_ADD(DATE '2026-01-31', INTERVAL 1 MONTH) AS jan31_plus_1m

実行結果

plus_7d minus_1m jan31_plus_1m
2026-03-25 2026-02-18 2026-02-28

1月31日に1か月を足すと、2月31日は存在しないので、月末の2月28日になります。「先月の同じ日」と比べるときなどは、この動きに注意しましょう。

直近の期間で絞り込むときにもよく使います。

WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)

CURRENT_DATE('Asia/Tokyo') は、日本時間での今日の日付です。タイムゾーンを書かないとUTCの日付になり、日本時間の午前0時〜9時の間は「昨日」になってしまいます。

2つの日付の差(DATE_DIFF)

DATE_DIFF(後の日付, 前の日付, 単位) で、2つの日付の差を求めます。会員登録から初回購入までの日数を出してみます。

SELECT
  c.customer_id
  , c.signup_date
  , MIN(o.order_date) AS first_order
  , DATE_DIFF(MIN(o.order_date), c.signup_date, DAY) AS days_to_first
FROM
  shop.customers AS c
  INNER JOIN shop.orders AS o
    ON c.customer_id = o.customer_id
GROUP BY
  c.customer_id
  , c.signup_date
ORDER BY
  c.customer_id

実行結果

customer_id signup_date first_order days_to_first
C01 2025-04-10 2026-03-02 326
C02 2025-06-02 2026-03-03 274
C03 2025-08-15 2026-03-08 205
C05 2025-12-05 2026-03-10 95
C06 2026-01-12 2026-03-15 62
C07 2026-02-03 2026-03-28 53

最終購入からの経過日数(リーセンシー)

基準日(ここでは2026年3月31日)から、顧客ごとの最終購入日までの日数を出します。RFM分析の「R(Recency)」に当たる数字です。

SELECT
  customer_id
  , MAX(order_date) AS last_order
  , DATE_DIFF(DATE '2026-03-31', MAX(order_date), DAY) AS days_since
FROM
  shop.orders
GROUP BY
  customer_id
ORDER BY
  days_since

実行結果

customer_id last_order days_since
C01 2026-03-29 2
C07 2026-03-28 3
C03 2026-03-22 9
C05 2026-03-21 10
C09 2026-03-18 13
C06 2026-03-15 16
C02 2026-03-14 17

DATE_DIFF の単位を MONTH や WEEK にすると、月数・週数の差も出せます。

文字列と日付の変換(PARSE_DATE・FORMAT_DATE)

SELECT
  PARSE_DATE('%Y%m%d', '20260301') AS from_ga4
  , PARSE_DATE('%Y/%m/%d', '2026/03/01') AS from_slash
  , FORMAT_DATE('%Y年%m月%d日', DATE '2026-03-01') AS jp

実行結果

from_ga4 from_slash jp
2026-03-01 2026-03-01 2026年03月01日
関数 向き 使いどころ
PARSE_DATE(形式, 文字列) 文字列 → 日付 GA4の event_date('20260301')や、CSVから読み込んだ日付の文字列を日付型にする
FORMAT_DATE(形式, 日付) 日付 → 文字列 レポート用の表示(「2026年03月」など)を作る

よく使う形式の記号は、%Y(4けたの年)、%m(2けたの月)、%d(2けたの日)、%a(曜日の略称)です。

GA4のタイムスタンプを日本時間に直す

GA4のエクスポートデータの event_timestamp は、UTC(世界標準時)のマイクロ秒で入っています。日本時間で集計するには、タイムゾーンを指定して変換します。

SELECT
  TIMESTAMP_MICROS(1772319756000000) AS utc_ts
  , DATETIME(TIMESTAMP_MICROS(1772319756000000), 'Asia/Tokyo') AS jst
  , DATE(TIMESTAMP_MICROS(1772319756000000)) AS utc_date
  , DATE(TIMESTAMP_MICROS(1772319756000000), 'Asia/Tokyo') AS jst_date

実行結果

utc_ts jst utc_date jst_date
2026-02-28 23:02:36 UTC 2026-03-01T08:02:36 2026-02-28 2026-03-01

UTCでは2月28日の23時ですが、日本時間では3月1日の朝8時です。タイムゾーンを指定しないと、日付が1日ずれてしまいます。時間帯別の集計をする場合は、EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')) のように、日本時間に直してから時刻を取り出しましょう。

※GA4の event_date 列は、GA4のプロパティに設定したタイムゾーン(日本なら通常は日本時間)の日付です。

まとめ

やりたいこと 書き方
年・月・曜日を取り出す EXTRACT(YEAR / MONTH / DAYOFWEEK FROM 日付)
月初・週初にまとめる DATE_TRUNC(日付, MONTH)、DATE_TRUNC(日付, WEEK(MONDAY))
月末 LAST_DAY(日付, MONTH)
日付を足す・引く DATE_ADD(日付, INTERVAL 7 DAY)、DATE_SUB(…)
2つの日付の差 DATE_DIFF(後の日付, 前の日付, DAY)
文字列 → 日付 PARSE_DATE('%Y%m%d', 文字列)
日付 → 文字列 FORMAT_DATE('%Y年%m月', 日付)
日本時間の今日 CURRENT_DATE('Asia/Tokyo')
UTCのタイムスタンプを日本時間に DATETIME(TIMESTAMP_MICROS(…), 'Asia/Tokyo')

日付の関数は、「週の始まり」と「タイムゾーン」の2つさえ気をつければ、迷わず使えるようになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。