REPEAT関数とROW_NUMBER関数で柔軟な日付リストを生成する

2024-06-28

PostgreSQLで特定の範囲内の日付リストを取得する方法

GENERATE_SERIES関数は、指定された開始値と終了値の間の連続した日付のリストを生成します。構文は以下の通りです。

SELECT
  generate_series(start_date, end_date, interval '1 day') AS date_list;

start_date: 開始日付
end_date: 終了日付
interval '1 day': 日付間隔 (1日)

例:

2023年1月1日から2024年1月1日までの日付リストを取得する場合、以下のクエリを実行します。

SELECT
  generate_series('2023-01-01', '2024-01-01', interval '1 day') AS date_list;

BETWEEN句を使用して、特定の範囲内のレコードを抽出することができます。構文は以下の通りです。

SELECT *
FROM your_table
WHERE column_name BETWEEN start_date AND end_date;

your_table: 対象テーブル
column_name: 日付列名

"orders"テーブルの "order_date" 列が2023年1月1日から2024年1月1日までの注文をすべて取得する場合、以下のクエリを実行します。

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2024-01-01';

CTE (Common Table Expression) を使用する

WITH date_range AS (
  SELECT generate_series('2023-01-01', '2024-01-01', interval '1 day') AS date_list
)
SELECT *
FROM your_table
WHERE column_name IN (
  SELECT date_list
  FROM date_range
);

WITH date_range AS (
  SELECT generate_series('2023-01-01', '2024-01-01', interval '1 day') AS date_list
)
SELECT *
FROM orders
WHERE order_date IN (
  SELECT date_list
  FROM date_range
);

それぞれの方法には、以下のような長所と短所があります。

GENERATE_SERIES関数:
- 長所: シンプルでわかりやすい
- 短所: 複雑な日付範囲には不向き
BETWEEN句:
- 長所: 汎用性が高い
- 短所: 複雑なクエリになると読みづらくなる
CTE:
- 短所: 構文が複雑

上記を参考に、状況に応じて適切な方法を選択してください。

その他の注意点

PostgreSQLには、ここで説明した以外にも、日付操作に関する様々な関数や機能が用意されています。詳細は、PostgreSQLのマニュアルを参照してください。
クエリを実行する前に、テーブル構造やデータ型を確認してください。
必要な権限を持っていることを確認してください。

PostgreSQLで特定の範囲内の日付リストを取得する - サンプルコード

GENERATE_SERIES関数を使用する

-- 2023年1月1日から2024年1月1日までの日付リストを取得
SELECT
  generate_series('2023-01-01', '2024-01-01', interval '1 day') AS date_list;

BETWEEN句を使用する

-- "orders"テーブルの "order_date" 列が2023年1月1日から2024年1月1日までの注文をすべて取得
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2024-01-01';

-- 2023年1月1日から2024年1月1日までの日付リストを生成し、"orders"テーブルの該当する注文をすべて取得
WITH date_range AS (
  SELECT generate_series('2023-01-01', '2024-01-01', interval '1 day') AS date_list
)
SELECT *
FROM orders
WHERE order_date IN (
  SELECT date_list
  FROM date_range
);

説明

上記のコードは、PostgreSQL 14.2で動作確認しています。
実際のコードで使用する場合には、テーブル名や列名、日付範囲などを置き換えてください。
生成される日付リストの形式は、YYYY-MM-DDとなります。

補足

サンプルコードでは、いずれの方法も単一のテーブルからレコードを取得していますが、JOIN句などを組み合わせて複数のテーブルからレコードを取得することも可能です。
より複雑な日付範囲を指定したい場合は、WHERE句に条件を追加したり、サブクエリを使用したりすることができます。

PostgreSQLで特定の範囲内の日付リストを取得する - その他の方法

EXTRACT関数とROW_NUMBER関数を使用する

この方法は、EXTRACT関数を使用して日付列から月と年を抽出し、ROW_NUMBER関数を使用して各月の日付を連番で割り当てることで、日付リストを生成します。構文は以下の通りです。

SELECT
  date_trunc('month', date_column) AS month,
  date_column AS date,
  ROW_NUMBER() OVER (PARTITION BY date_trunc('month', date_column) ORDER BY date_column) AS row_num
FROM your_table
WHERE date_column BETWEEN start_date AND end_date
ORDER BY month, row_num;

date_column: 日付列名

SELECT
  date_trunc('month', order_date) AS month,
  order_date AS date,
  ROW_NUMBER() OVER (PARTITION BY date_trunc('month', order_date) ORDER BY order_date) AS row_num
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2024-01-01'
ORDER BY month, row_num;

SELECT
  date_trunc('day', generate_series(
    date_trunc('month', start_date),
    date_trunc('month', end_date) + interval '1 month',
    interval '1 month'
  )::date) AS date,
  ROW_NUMBER() OVER (ORDER BY date) AS row_num
FROM your_table
WHERE date_column BETWEEN date AND date + interval '1 day' - interval '1 second';

SELECT
  date_trunc('day', generate_series(
    date_trunc('month', '2023-01-01'),
    date_trunc('month', '2024-01-01') + interval '1 month',
    interval '1 month'
  )::date) AS date,
  ROW_NUMBER() OVER (ORDER BY date) AS row_num
FROM orders
WHERE date_column BETWEEN date AND date + interval '1 day' - interval '1 second';