SQLの日付関数の使い方【BigQuery版:DATE_TRUNC・DATE_DIFF・FORMAT_DATE・タイムゾーン】
売上の月別集計、曜日別の比較、最終購入からの経過日数──データ分析では、日付の扱いが欠かせません。ところが、日付の関数はデータベースによって書き方が違い、迷いやすいところでもあります。
この記事では、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つさえ気をつければ、迷わず使えるようになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
