PostgreSQLで連番作成の方法は？任意の日付・曜日・時間範囲も簡単に生成！カレンダーも作成できる

PostgreSQL generate_series関数について Database
2024.12.06

PostgreSQLで連番を生成して利用したい時や、特定の条件に従って日付順のカレンダーを作成したい時があります。そのような場合には、PostgreSQLに備わっている連番生成機能を利用すると、とても簡単に連番を作成したり、一連の日付を生成することが可能です。この記事では連番生成機能の基本的な使い方から応用までを解説します。

generate_series関数とは？

PostgreSQLデータベースには generate_series という関数が用意されており、この関数を使うことで非常に簡単に連番を生成することが出来ます。連番を生成できれば、それを応用してカレンダーを生成したりすることも可能です。

generate_series関数の使い方

generate_series関数には引数の異なる3種類の関数が用意されています。これらを使い分けることで、任意の連番を生成することが可能となっています。それぞれの関数を使い分けることで、任意の連番を生成することが出来ます。

関数説明
generate_series(start, stop)startからstopまで、刻み１で連続する値を生成します。
generate_series(start, stop, step)startからstopまで、刻みstepで連続する値を生成します。
generate_series(start, stop, step interval)startからstopまで、刻みstepで連続する値を生成します。

generate_series関数を実際に使ってみる

1～10までの連番を作成する

シンプルに1～10までの連番を生成する場合、次のようにgenerate_series関数を呼び出します。連番開始は「1」、連番終了は「10」としてgenerate_series関数を呼び出します。

SELECT * FROM generate_series(1, 10);
+-----------------+
| generate_series |
+-----------------+
|               1 |
|               2 |
|               3 |
|               4 |
|               5 |
|               6 |
|               7 |
|               8 |
|               9 |
|              10 |
+-----------------+
(10 rows)

1～10までの連番を2刻みで作成する

1～10までの数値を2刻みで生成するには、generate_series関数のgenerate_series(start, stop, step)を使います。「step」に2を設定することで、2刻みで連番を生成してくれます。

SELECT * FROM generate_series(1, 10, 2);
+-----------------+
| generate_series |
+-----------------+
|               1 |
|               3 |
|               5 |
|               7 |
|               9 |
+-----------------+
(5 rows)

10から1までの連番を降順で生成する

連番は降順で生成することが可能となっています。generate_series(start, stop, step)関数の「step」引数に負数を設定することで降順で連番を生成することが出来ます。ここでは-1を設定しています。

SELECT * FROM generate_series(10, 1, -1);
+-----------------+
| generate_series |
+-----------------+
|              10 |
|               9 |
|               8 |
|               7 |
|               6 |
|               5 |
|               4 |
|               3 |
|               2 |
|               1 |
+-----------------+
(10 rows)

10から1までの連番を-2刻みの降順で生成する

降順で連番を生成しますが、「step」に-2を設定することで10～1までの連番を-2刻みで生成することも出来ます。

SELECT * FROM generate_series(10, 1, -2);
+-----------------+
| generate_series |
+-----------------+
|              10 |
|               8 |
|               6 |
|               4 |
|               2 |
+-----------------+
(5 rows)

任意の日付範囲を表示する

任意の日付範囲のデータを生成することも出来ます。開始日(2024-11-25)、終了日(2024-12-05)、日付間隔(1日)を設定することによって簡単に日付範囲の一覧を生成することが出来ます。

SELECT
    a::date
FROM generate_series
        ('2024-11-25'::date,
         '2024-12-05'::date,
         '1 day'::interval) a;
+------------+
|     a      |
+------------+
| 2024-11-25 |
| 2024-11-26 |
| 2024-11-27 |
| 2024-11-28 |
| 2024-11-29 |
| 2024-11-30 |
| 2024-12-01 |
| 2024-12-02 |
| 2024-12-03 |
| 2024-12-04 |
| 2024-12-05 |
+------------+
(11 rows)

任意の時間範囲を表示する

任意の時間範囲のデータを生成することも出来ます。開始日時(2024-11-25 04:00:00)、終了日時(2024-11-27 13:00:00)、時間間隔(1時間)を設定することによって簡単に時間範囲の一覧を生成することが出来ます。

SELECT *
FROM generate_series
        ('2024-11-25 04:00:00'::timestamp,
         '2024-11-27 13:00:00'::timestamp,
         '1 hour'::interval);
+---------------------+
|   generate_series   |
+---------------------+
| 2024-11-25 04:00:00 |
| 2024-11-25 05:00:00 |
| 2024-11-25 06:00:00 |
| 2024-11-25 07:00:00 |
| 2024-11-25 08:00:00 |
| 2024-11-25 09:00:00 |
| 2024-11-25 10:00:00 |
| 2024-11-25 11:00:00 |
| 2024-11-25 12:00:00 |
| 2024-11-25 13:00:00 |
| 2024-11-25 14:00:00 |
| 2024-11-25 15:00:00 |
| 2024-11-25 16:00:00 |
| 2024-11-25 17:00:00 |
| 2024-11-25 18:00:00 |
| 2024-11-25 19:00:00 |
| 2024-11-25 20:00:00 |
| 2024-11-25 21:00:00 |
| 2024-11-25 22:00:00 |
| 2024-11-25 23:00:00 |
| 2024-11-26 00:00:00 |
| 2024-11-26 01:00:00 |
| 2024-11-26 02:00:00 |
| 2024-11-26 03:00:00 |
| 2024-11-26 04:00:00 |
| 2024-11-26 05:00:00 |
| 2024-11-26 06:00:00 |
| 2024-11-26 07:00:00 |
| 2024-11-26 08:00:00 |
| 2024-11-26 09:00:00 |
| 2024-11-26 10:00:00 |
| 2024-11-26 11:00:00 |
| 2024-11-26 12:00:00 |
| 2024-11-26 13:00:00 |
| 2024-11-26 14:00:00 |
| 2024-11-26 15:00:00 |
| 2024-11-26 16:00:00 |
| 2024-11-26 17:00:00 |
| 2024-11-26 18:00:00 |
| 2024-11-26 19:00:00 |
| 2024-11-26 20:00:00 |
| 2024-11-26 21:00:00 |
| 2024-11-26 22:00:00 |
| 2024-11-26 23:00:00 |
| 2024-11-27 00:00:00 |
| 2024-11-27 01:00:00 |
| 2024-11-27 02:00:00 |
| 2024-11-27 03:00:00 |
| 2024-11-27 04:00:00 |
| 2024-11-27 05:00:00 |
| 2024-11-27 06:00:00 |
| 2024-11-27 07:00:00 |
| 2024-11-27 08:00:00 |
| 2024-11-27 09:00:00 |
| 2024-11-27 10:00:00 |
| 2024-11-27 11:00:00 |
| 2024-11-27 12:00:00 |
| 2024-11-27 13:00:00 |
+---------------------+
(58 rows)

もちろん、分単位の時間を生成することも可能です。

SELECT *
FROM generate_series
        ('2024-11-25 04:00:00'::timestamp,
         '2024-11-25 05:30:00'::timestamp,
         '10 minutes'::interval);
+---------------------+
|   generate_series   |
+---------------------+
| 2024-11-25 04:00:00 |
| 2024-11-25 04:10:00 |
| 2024-11-25 04:20:00 |
| 2024-11-25 04:30:00 |
| 2024-11-25 04:40:00 |
| 2024-11-25 04:50:00 |
| 2024-11-25 05:00:00 |
| 2024-11-25 05:10:00 |
| 2024-11-25 05:20:00 |
| 2024-11-25 05:30:00 |
+---------------------+
(10 rows)

今月の月初から月末までの日付の “日” を全て表示する

generate_series関数を応用すると、今月の日付の日を一覧表示することが出来ます。以下の例では2024年12月05日に関数を実行しましたので、2024年12月の日が1～31まで列挙されています。

SELECT
    s.a
FROM
    generate_series(
        (SELECT extract('day' FROM date_trunc('month', current_date)))::int,
        (SELECT extract('day' FROM (date_trunc('month', current_date + interval '1 month') - interval '1 day')))::int
    ) AS s(a);

+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
+----+
(31 rows)

今週の日曜～土曜の一覧を生成する

以下のように、今週の日曜日から土曜日までの日付の一覧を表示することも出来ます。

SELECT
    current_date + s.a AS date
FROM
    generate_series(
        (SELECT 0 - extract(dow FROM current_date))::int,
        (SELECT 6 - extract(dow FROM current_date))::int
    ) AS s(a);

+------------+
|    date    |
+------------+
| 2024-12-01 |
| 2024-12-02 |
| 2024-12-03 |
| 2024-12-04 |
| 2024-12-05 |
| 2024-12-06 |
| 2024-12-07 |
+------------+
(7 rows)

SELECT current_date;
+--------------+
| current_date |
+--------------+
| 2024-12-05   |
+--------------+
(1 row)

今月の月初から月末までの日付を全て表示する

今月の月初日から月末日までの日付の一覧を生成することも出来ます。

SELECT
    (SELECT date_trunc('month', current_date)::date) + (s.a - 1)
FROM
    generate_series(
        (SELECT extract('day' FROM date_trunc('month', current_date)))::int,
        (SELECT extract('day' FROM (date_trunc('month', current_date + interval '1 month') - interval '1 day')))::int
    ) AS s(a);

+------------+
|  ?column?  |
+------------+
| 2024-12-01 |
| 2024-12-02 |
| 2024-12-03 |
| 2024-12-04 |
| 2024-12-05 |
| 2024-12-06 |
| 2024-12-07 |
| 2024-12-08 |
| 2024-12-09 |
| 2024-12-10 |
| 2024-12-11 |
| 2024-12-12 |
| 2024-12-13 |
| 2024-12-14 |
| 2024-12-15 |
| 2024-12-16 |
| 2024-12-17 |
| 2024-12-18 |
| 2024-12-19 |
| 2024-12-20 |
| 2024-12-21 |
| 2024-12-22 |
| 2024-12-23 |
| 2024-12-24 |
| 2024-12-25 |
| 2024-12-26 |
| 2024-12-27 |
| 2024-12-28 |
| 2024-12-29 |
| 2024-12-30 |
| 2024-12-31 |
+------------+
(31 rows)

今月の月初から月末までの日付のうち、平日のみ全て表示する

さらに今月の月初日から月末までの日付のうち、平日のみの日付を表示することも可能です。それぞれの日付の曜日を取得して、土曜日と日曜日を除外するSQLで実現できます。

SELECT x.y
FROM (
    SELECT
        (SELECT date_trunc('month', current_date)::date) + (s.a - 1)
         FROM generate_series(
            (SELECT extract('day' FROM date_trunc('month', current_date)))::int,
            (SELECT extract('day' FROM (date_trunc('month', current_date + interval '1 month') - interval '1 day')))::int
    ) AS s(a)
) x(y)
WHERE (SELECT extract('dow' FROM x.y) NOT IN (0, 6))
ORDER BY x.y;

+------------+
|     y      |
+------------+
| 2024-12-02 |
| 2024-12-03 |
| 2024-12-04 |
| 2024-12-05 |
| 2024-12-06 |
| 2024-12-09 |
| 2024-12-10 |
| 2024-12-11 |
| 2024-12-12 |
| 2024-12-13 |
| 2024-12-16 |
| 2024-12-17 |
| 2024-12-18 |
| 2024-12-19 |
| 2024-12-20 |
| 2024-12-23 |
| 2024-12-24 |
| 2024-12-25 |
| 2024-12-26 |
| 2024-12-27 |
| 2024-12-30 |
| 2024-12-31 |
+------------+
(22 rows)

まとめ

generate_series関数を利用することで、単純な昇順・降順の連番を生成することも出来ますし、連番生成機能を応用することで連続した日付を生成することも可能です。非常に便利な関数ですので、この記事で挙げた例を応用することで様々な状況に活かすことが出来ます。

タイトルとURLをコピーしました