PostgreSQLで時系列データを時間間隔でグルーピングする方法【generate_series】

センサーデータやアクセスログを分析しているとき「30分ごとに集計したい」「1時間単位でまとめたい」と思うことってありません？

GROUP BYに日時カラムをそのまま突っ込むとレコード単位でバラバラになります。かといってアプリ側でループして集計するのも……なんか違う気がしませんか。

PostgreSQLにはgenerate_seriesという便利な関数があって、任意の時間間隔のタイムスタンプ連続値を生成してLEFT JOINで集計するという手法でこの悩みをSQLだけで解決できます。しかもデータが存在しない時間帯も「0件」として出力できるのがgenerate_seriesの便利なところですね！

このページではgenerate_seriesを使ったタイムスタンプのグルーピング方法を、実際に動かして確認しながら解説します。

この記事でわかること

• PostgreSQLで任意の時間間隔（10分・30分・1時間など）に時系列データをグルーピングするSQLの書き方。
• generate_series関数の仕組みとタイムスタンプへの活用方法。
• データが存在しない時間帯も「0件」として表示する方法（LEFT JOINの活用）。
• テーブル作成〜テストデータ挿入〜集計クエリまでの完全な実装例。

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'           -- ← ここを変えると間隔が変わる
    ) 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)

公式ドキュメントはこちらで確認できます。
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分ごとの件数が出来上がりです。

間隔を変えるだけで柔軟に対応できる

intervalの値を変えるだけでさまざまな集計粒度に対応できます。

実際にやってみる

アクセスログを想定したテーブルを作成しテストデータを挿入して、集計クエリを動かしてみます。

Step 1：テーブルを作成する

1日分のアクセス数を30分ごとに集計するパターンで試します。タイムスタンプだけで十分だったのにアクセスログっぽい感じにしたくてユーザー名まで入れてしまいました。

CREATE TABLE access_log (
    id          BIGSERIAL
    , user_name VARCHAR(255)
    , access_time TIMESTAMP
);

CREATE TABLE

ローカル環境でPostgreSQLを動かすにはDockerが便利です。以下の記事も参考にどうぞ。

Step 2：テストデータを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 10000

10件ほど確認してみると分単位のランダムなタイムスタンプになっていることが確認できます。

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:00

Step 3：30分ごとの件数を集計する

当日1日分を30分ごとに集計するクエリを実行します。date_trunc('day', now())で今日の00:00:00を動的に取得しているので日付をハードコードしなくて済みます。

SELECT
    to_char(time_table, 'hh24:mi') AS time_slot
    , count(access_log.access_time) AS cnt
FROM
    generate_series(
        date_trunc('day', now())
        , date_trunc('day', now()) + interval '23 hour 59 minute 59 second'
        , interval '30 minute'
    ) AS time_table
    LEFT JOIN access_log ON (
        access_log.access_time >= time_table
        AND access_log.access_time < time_table + interval '30 minute'
    )
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     | 207

この結果をそのままExcelに貼り付けると簡単にグラフ化できますね！

報告資料づくりに直結してくれるのも generate_seriesの便利なところですね！

Q&A

特定の日付範囲を指定したい

date_trunc('day', now())の部分をリテラルのタイムスタンプに変えるだけでOKです。

SQL

generate_series(
    '2025-11-01 00:00:00'::timestamp
    , '2025-11-01 23:59:59'::timestamp
    , interval '30 minute'
)

timestamp with time zone（timestamptz）でも使える？

使えます。generate_seriesはタイムスタンプ型・タイムゾーン付きタイムスタンプ型の両方をサポートしています。引数とテーブルのカラムの型を合わせておくと暗黙的な型変換によるインデックスへの影響を避けられます。

大量データで実行速度が遅い場合の対策は？

access_timeカラムにインデックスを張るとJOINのパフォーマンスが大幅に改善します。

SQL

CREATE INDEX idx_access_log_access_time ON access_log (access_time);

集計対象の期間が広い場合はパーティショニングも検討してみてください。

データが存在しない時間帯が出力されない

JOINがLEFT JOINではなくINNER JOINになっている可能性があります。確認してみてください。またcount(*)ではなくcount(対象カラム)を使うことで、JOINが成立しなかった行（NULLの行）は自動的に0として扱われるので、こちらも合わせて確認してみてください。

PostgreSQLやDocker環境構築の記事をまとめて確認したい場合は、データベース記事一覧もどうぞ。

ちゅんろぐ

データベースの使い方・環境構築まとめ｜PostgreSQL・Docker・SQL Server 「SQLの書き方どうだったっけ？」「Docker でDB立ち上げるときの手順、また忘れた……」となることってありますよね。 このページでは当ブログで解説しているデータベース関…

まとめ

• generate_seriesでタイムスタンプの連続値を生成してそれを軸にLEFT JOINすることで、任意の時間間隔での集計が実現できるよ。
• intervalの値を変えるだけで10分・30分・1時間など粒度を自由に切り替えできるので使い回しがきいて便利だよ。
• LEFT JOINを使うことでデータが存在しない時間帯も0件として出力されるのがうれしいポイントだよ。
• date_trunc('day', now())で動的に今日の開始日時を取得するようにしておくと日付のハードコードを避けられてスマートだよ。
• 集計結果をそのままExcelに貼り付けてグラフ化できるから報告資料づくりにも直結してくれるよ。