PostgreSQLである期間の集計を今の時間のちょっと前に行いたい。

# 目的
うまいタイトルが付けられなかったんですが・・・例えば5分ごとの集計を行いたい時に、今の時間はまだデータがたまっていないので、その前の期間の5分を集計したいという要件です。
バッチの開始時間が10:01だったとすると、集計したい期間は9:55-10:00までになります。10:06なら10:00-10:05、10:07も10:00-10:05です。

# コード

履歴テーブルを上記のような時間で取得するストアードプロシージャを書きました。
get_normalize_dateは現在の時間から指定している期間の前の終了日時を取得する関数になっています。

```sql
DROP TABLE IF EXISTS public.histories CASCADE;
CREATE TABLE public.histories (
  uuid UUID NOT NULL DEFAULT gen_random_uuid()
  ,value TEXT NOT NULL DEFAULT ''
  ,created_at TIMESTAMPTZ
  ,PRIMARY KEY(uuid)
);

INSERT INTO public.histories(value, created_at)
SELECT
  t1.ts::TEXT
  ,t1.ts
FROM
  generate_series(
    '2022-04-01 00:00'::timestamp,
    '2022-04-01 01:00', '1 minutes'
  ) AS t1(ts);
```

```sql
CREATE OR REPLACE FUNCTION list_histories(
  p_ts TIMESTAMPTZ
  ,p_period_interval INTERVAL
) RETURNS SETOF histories AS $$
DECLARE
  w_start_ts TIMESTAMPTZ;
  w_end_ts TIMESTAMPTZ;
BEGIN
  w_end_ts := get_normalize_date(p_ts, p_period_interval);
  w_start_ts := w_end_ts - p_period_interval;
  RETURN QUERY SELECT
    *
  FROM
    histories
  WHERE
    created_at >= w_start_ts
    AND created_at < w_end_ts
  ;
END;
$$ LANGUAGE plpgsql;
```

結果

```text
select list_histories('2022-04-01 00:06:00', '5 minutes');
                                    list_histories                                     
---------------------------------------------------------------------------------------
 (0c923b2a-bc75-4836-af6e-1274ff04b946,"2022-04-01 00:00:00","2022-04-01 00:00:00+09")
 (2ba9548f-c753-406b-9268-abb89c12aeda,"2022-04-01 00:01:00","2022-04-01 00:01:00+09")
 (ba3ee225-dc71-42e8-8d26-36d28397f2ea,"2022-04-01 00:02:00","2022-04-01 00:02:00+09")
 (26a32c95-37ed-44d3-9dbc-cc5a92438910,"2022-04-01 00:03:00","2022-04-01 00:03:00+09")
 (fe899271-9cad-4cc0-b429-29df3e11a719,"2022-04-01 00:04:00","2022-04-01 00:04:00+09")
(5 rows)

select list_histories('2022-04-01 00:11:00', '5 minutes');
                                    list_histories                                     
---------------------------------------------------------------------------------------
 (72e96d87-2280-4a08-ba4b-c58617827ba7,"2022-04-01 00:05:00","2022-04-01 00:05:00+09")
 (0e2c755d-fb4e-4bbb-b780-35896274dbbc,"2022-04-01 00:06:00","2022-04-01 00:06:00+09")
 (9876292e-c2d0-4f9b-907e-1c66e601b0c5,"2022-04-01 00:07:00","2022-04-01 00:07:00+09")
 (a043ca31-ddb4-4589-8adc-148cab72146f,"2022-04-01 00:08:00","2022-04-01 00:08:00+09")
 (226fdfed-05cc-4462-aea3-75deb7e264f1,"2022-04-01 00:09:00","2022-04-01 00:09:00+09")
```

では、get_normalize_dateを作ります。

```sql
CREATE OR REPLACE FUNCTION get_normalize_date(
  p_ts TIMESTAMPTZ
  ,p_period_interval INTERVAL
) RETURNS TIMESTAMPTZ AS $$
DECLARE
  w_ts TIMESTAMPTZ;
  w_period_seconds BIGINT := EXTRACT(EPOCH FROM p_period_interval);
  w_seconds BIGINT;
BEGIN
  w_ts := date_trunc('second', p_ts);
  w_seconds = date_part('second', w_ts);
  IF w_period_seconds > 60 THEN
    w_seconds = w_seconds + date_part('minute', w_ts) * 60;
  END IF;
  IF w_period_seconds > 3600 THEN
    w_seconds = w_seconds + date_part('hour', w_ts) * 3600;
  END IF;
  RETURN w_ts - ((w_seconds % w_period_seconds) || ' second')::INTERVAL;
END;
$$ LANGUAGE plpgsql;
```

ちょっと大変ですね。ちなみにPostgreSQL14から使えるようになった[date_bin](https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2021/08/pg14_report_20210818.pdf)があれば簡単です。

```sql
w_end_ts := date_bin(p_period_interval, p_ts, '2022-01-01');
```