PostgreSQLで30分ごとなど任意の間隔に集計しデータがない時間帯も0件で表示したい場合はgenerate_seriesで時間軸を作って対象テーブルをLEFT JOINします。0件の時間帯を補う必要がない場合はdate_binを使うとより短いSQLで集計できます。
date_trunc・date_bin・generate_seriesの使い分け
既存データを30分単位にまとめるだけならdate_binでも集計できます。でもこっちはデータが1件もない時間帯は結果に現れません。00:30のデータがなくても「00:30|0件」のように行を残したい場合にgenerate_seriesとLEFT JOINの組み合わせが役立ちます。
| 方法 | 向いているケース | 30分単位 | データなしを0件表示 |
|---|---|---|---|
date_trunc | 時・日・月などの単位でまとめる | △ | × |
date_bin | 15分・30分など任意間隔でまとめる | ○ | × |
generate_series + LEFT JOIN | 欠損時間帯も含めたい | ○ | ○ |
0件の時間帯を補わない場合はPostgreSQL 14以降のdate_binで30分単位にまとめられます。後半で作成するaccess_logを使うと当日分の集計は次のように書けます。
SELECT
date_bin(
interval '30 minute'
, access_time
, CURRENT_DATE::timestamp
) AS time_slot
, count(*) AS cnt
FROM
access_log
WHERE
access_time >= CURRENT_DATE::timestamp
AND access_time < CURRENT_DATE::timestamp + interval '1 day'
GROUP BY
1
ORDER BY
1;第3引数は時間を区切る基準点です。この例では当日の00:00を基準に30分ずつ区切ります。データがない時間帯の行は生成されません。なお、date_binの間隔には月以上の単位を指定できません。
generate_seriesを使ったグルーピングの基本
考え方の概要
たとえば1日分のアクセスログを30分ごとに集計したい場合、最終的に欲しい結果はこういう形ですよね。
time_slot | cnt
-----------+-----
00:00 | 197
00:30 | 199
01:00 | 202
...(以下省略)この「30分刻みの時間軸」を自分で作り出してしまうのがgenerate_series関数の役割です。生成した時間軸を基準にして実テーブルをLEFT JOINすることで、データが存在しない時間帯も0件として取得できます。
SQLの全体像(テンプレート)
30分間隔でグルーピングする汎用テンプレートです。【 】で囲んだ箇所を自身の環境に合わせて書き換えてください。
SELECT
to_char(time_table, 'hh24:mi') AS time_slot
, count(【対象カラム】) AS cnt
FROM
generate_series(
【開始タイムスタンプ】 -- 例: '2025-11-28 00:00:00'::timestamp
, 【終了タイムスタンプ】 -- 例: '2025-11-28 23:59:59'::timestamp
, interval '30 minute' -- JOIN条件の時間幅も同じ値にする
) AS time_table
LEFT JOIN 【対象テーブル】 ON (
【対象カラム】 >= time_table
AND 【対象カラム】 < time_table + interval '30 minute'
)
GROUP BY
time_table
ORDER BY
time_table;LEFT JOINを使う理由
INNER JOINにするとデータが存在しない時間帯の行が消えてしまいます。グラフ化や外部ツールへの連携を想定する場合は0件でも行を出力するためにLEFT JOINを使うのがベターです。
generate_series関数とは
基本的な動作
generate_seriesは開始値から終了値までを指定の間隔で連続生成する集合返却関数(Set Returning Function)です。タイムスタンプ型にも対応していてFROM句に置くと1カラムのテーブルのように扱えます。
generate_series(start, stop, step interval)
| 引数 | 型 | 説明 |
|---|---|---|
| start | timestamp / timestamptz | 開始日時。 |
| stop | timestamp / timestamptz | 生成する日時の上限。刻み幅に一致する場合は、この日時も結果に含まれます。各時間枠で数える範囲はJOIN条件で決まります。 |
| step | interval | 刻み幅。 |
公式ドキュメントはこちらで確認できます。
PostgreSQL: Documentation: 18: 9.26. Set Returning Functions
生成されるタイムスタンプのイメージ
30分間隔で'2025-11-28 00:00:00'〜'2025-11-28 01:30:00'を指定すると次のような値が生成されます。
2025-11-28 00:00:00
2025-11-28 00:30:00
2025-11-28 01:00:00
2025-11-28 01:30:00これを軸にLEFT JOINすると各行にマッチしたレコードが紐付いてきます。
time_table | access_time
---------------------+---------------------
2025-11-28 00:00:00 | 2025-11-28 00:04:00
2025-11-28 00:00:00 | 2025-11-28 00:16:00
2025-11-28 00:00:00 | 2025-11-28 00:29:00
2025-11-28 00:30:00 | 2025-11-28 00:35:00
2025-11-28 00:30:00 | 2025-11-28 00:51:00
2025-11-28 01:00:00 | 2025-11-28 01:00:00
...あとはtime_tableでGROUP BY&COUNTするだけで30分ごとの件数が出来上がりです。
間隔を変えるだけで柔軟に対応できる
集計間隔を変えるときはgenerate_seriesの第3引数とJOIN条件で加算する時間幅を同じ値にします。15分ごとなら両方をinterval '15 minutes'にしてください。片方だけ変えると同じデータを重複して数えたり集計されない時間帯ができたりします。
| 集計粒度 | intervalの記述例 |
|---|---|
| 10分ごと | interval '10 minute' |
| 15分ごと | interval '15 minute' |
| 30分ごと | interval '30 minute' |
| 1時間ごと | interval '1 hour' |
| 1日ごと | interval '1 day' |
実際にやってみる
アクセスログを想定したテーブルを作成しテストデータを挿入して、集計クエリを動かしてみます。
テーブルを作成する
1日分のアクセス数を30分ごとに集計するパターンで試します。タイムスタンプだけで十分だったのにアクセスログっぽい感じにしたくてユーザー名まで入れてしまいました。
CREATE TABLE access_log (
id BIGSERIAL
, user_name VARCHAR(255)
, access_time TIMESTAMP
);CREATE TABLEローカル環境でPostgreSQLを動かすにはDockerが便利です。以下の記事も参考にどうぞ。

テストデータを10,000件作成する
1分単位のランダムなタイムスタンプを1万件ほど作成します。user_nameはmd5で適当な文字列を入れています。余計なものを入れるからこうなる……
INSERT INTO access_log (user_name, access_time)
SELECT
md5(clock_timestamp()::TEXT)
, date_trunc('day', now()) + trunc(random() * 60 * 24) * interval '1 minute'
FROM
generate_series(1, 10000);INSERT 0 1000010件ほど確認してみると分単位のランダムなタイムスタンプになっていることが確認できます。
SELECT * FROM access_log LIMIT 10; id | user_name | access_time
----+----------------------------------+---------------------
1 | a02f0a047a994ee920cfdb7940cecef8 | 2025-11-28 03:41:00
2 | bb46b16afc0737f4aeb6565d8b556474 | 2025-11-28 05:21:00
3 | 9e60031d328a3cf28321f8425a6e7142 | 2025-11-28 13:20:00
4 | 927c172e60ca72524f7990e43b0b332e | 2025-11-28 15:32:00
5 | e5e03ff6319232121ae27af22a3e7828 | 2025-11-28 20:25:00
6 | fbd4c4d1b34aa66311fd40a38ecbca5c | 2025-11-28 05:07:00
7 | 0a0ac15ac15790d15246cfdb5a1d03e4 | 2025-11-28 16:39:00
8 | c981fa58ced8d1187c2a3821e9e2375e | 2025-11-28 00:54:00
9 | c27e463b24d6296afcd3ae08071823b0 | 2025-11-28 09:02:00
10 | fb102f549419560de544a7ace61935cc | 2025-11-28 11:32:0030分ごとの件数を集計する
当日1日分を30分ごとに集計します。CURRENT_DATE::timestampで当日の00:00を取得し、23:30までの48個の時間枠を作ります。各時間枠は開始時刻を含み30分後の時刻を含まない条件で数えるため、00:30ちょうどのデータは00:30の枠に入ります。
SELECT
to_char(time_table, 'hh24:mi') AS time_slot,
count(access_log.access_time) AS cnt
FROM
generate_series(
CURRENT_DATE::timestamp,
CURRENT_DATE::timestamp + interval '23 hours 30 minutes',
interval '30 minutes'
) AS time_table
LEFT JOIN access_log ON (
access_log.access_time >= time_table
AND access_log.access_time < time_table + interval '30 minutes'
)
GROUP BY
time_table
ORDER BY
time_table; time_slot | cnt
-----------+-----
00:00 | 197
00:30 | 199
01:00 | 202
01:30 | 217
02:00 | 218
...(中略)
23:00 | 224
23:30 | 207Excelとかでグラフ化に利用しやすい!
この結果をそのままExcelに貼り付けると簡単にグラフ化できますね!

報告資料づくりに直結してくれるのも generate_seriesの便利なところですね!
0件の時間帯と境界時刻を確認する
0件の時間帯も残ることを確認するため、2件だけのデータで集計してみます。このSQLはテーブルを作成・変更せずに実行できます。00:30台のデータを入れず、01:00ちょうどのデータがどの枠に入るかも確認します。
WITH sample_log(access_time) AS (
VALUES
('2025-11-28 00:10:00'::timestamp)
, ('2025-11-28 01:00:00'::timestamp)
)
SELECT
to_char(time_table, 'hh24:mi') AS time_slot
, count(sample_log.access_time) AS cnt
FROM
generate_series(
'2025-11-28 00:00:00'::timestamp
, '2025-11-28 01:00:00'::timestamp
, interval '30 minute'
) AS time_table
LEFT JOIN sample_log ON (
sample_log.access_time >= time_table
AND sample_log.access_time < time_table + interval '30 minute'
)
GROUP BY
time_table
ORDER BY
time_table; time_slot | cnt
-----------+-----
00:00 | 1
00:30 | 0
01:00 | 1
(3 rows)00:30の行は残り件数が0になります。01:00ちょうどのデータは01:00の枠に入ります。
迷いやすいポイント
PostgreSQLやDocker環境構築の記事をまとめて確認したい場合はデータベース記事一覧もどうぞ。

まとめ
generate_seriesでタイムスタンプの連続値を生成してそれを軸にLEFT JOINすることで、任意の時間間隔での集計が実現できるよ。intervalの値を変えるだけで10分・30分・1時間など粒度を自由に切り替えできるので使い回しがきいて便利だよ。- LEFT JOINを使うことでデータが存在しない時間帯も0件として出力されるのがうれしいポイントだよ。
- 実践例では
CURRENT_DATE::timestampで当日の00:00を取得しているので毎回日付を書き換えずに集計できるよ。 - 集計結果をそのままExcelに貼り付けてグラフ化できるから報告資料づくりにも直結してくれるよ。


