実務でよく使う！ PostgreSQLで日時を扱うTIPS7選

こんにちは、飯塚です。

普段の業務の中で１分値、３０分値など、時間とともに変わるデータを扱うことが多いです。今回は日時の加算や、文字列⇔日付型の変換など、時間に依存するテーブルを扱う場合に、知っておくと便利なテクニックを備忘録としてまとめてみました。

PostgreSQLで日時を管理するときの型

はじめてPostgreSQLを触ったときに、時間を扱う型の種類を知らなくて混乱したのでまとめました。基本この４つだけ知ってればイケるはず。

型	例
TIMESTAMP型	2019-03-01 10:00:00
Date型	2019-03-01
Time型	10:00:00
文字列	Mar 1, 20190301, …etc

日付（Date型）と時刻（TIME型）の結合

Date型 + Time型 = TIMESTAMP型

SELECT column_date + column_time FROM table;

1	SELECT column_date + column_time FROM table;

SELECT * FROM table
WHERE column_date + column_time <= '2019/03/01 10:00:00';

1 2	SELECT * FROM table WHERE column_date + column_time <= '2019/03/01 10:00:00';

Where句で使うと日付を跨いだ範囲指定にも対応できるのが嬉しいです。取得するレコード数が多い場合、Where句で使用するとパフォーマンスが激遅になるので注意です！

日時加算、減算をする interval

(TIMESTAMP, DATE, TIME) + interval ‘N xxxxxx’

SELECT column_time + interval '30 minutes' FROM table;

1	SELECT column_time + interval '30 minutes' FROM table;

TIMESTAMP型、DATE型、TIME型で使用可能です。intervalで加減算できる時間の単位（hour, minutesなど）は以下参照。
日付 / 時刻関数と演算子

開始時間+経過時間から終了時間を計算

interval ‘N xxxxxx’ * 経過時間

SELECT column_timestamp_from + interval '1 minutes' * column_duration
 AS end_datetime FROM table;

1 2	SELECT column_timestamp_from + interval '1 minutes' * column_duration AS end_datetime FROM table;

時間幅を持っているテーブルに有効です。

TIMESTAMP型から日付、時刻を取り出す

SELECT COLUMN(column_timestamp AS DATE) FROM table;

1	SELECT COLUMN(column_timestamp AS DATE) FROM table;

よく文字列に一旦変換するSQLを見かけますが、「COLUMN ~ AS XXX」の書き方が推奨されています。

文字列→TIMESTAMP

SELECT TO_TIMESTAMP(column_timestamp_str, 'YYYY-MM-DD HH24:MI:00') FROM table;

1	SELECT TO_TIMESTAMP(column_timestamp_str, 'YYYY-MM-DD HH24:MI:00') FROM table;

直観的でわかりやすいですね！

文字列→日付

SELECT TO_DATE(column_date_str, 'YYYY-MM-DD') FROM table;

1	SELECT TO_DATE(column_date_str, 'YYYY-MM-DD') FROM table;

これもそのままという感じ。

N分ごとに集計

１５分単位のデータを３０分単位に集計するイメージはこんな感じです。

元データ	2019-03-01 10:00:00 2019-03-01 10:15:00 2019-03-01 10:30:00 2019-03-01 10:45:00
集計後	2019-03-01 10:00:00 2019-03-01 10:30:00

15分ごとのTIMESTAMP型のデータを30分単位のコマに集計するSQLは以下です。

SELECT
   TO_TIMESTAMP((TO_CHAR(column_timestamp, 'YYYY-MM-DD HH24') || ':' || TO_CHAR( 
      TRUNC( EXTRACT('minute' FROM column_timestamp) / 30 ) * 30 , 'FM09' 
    )) , 'YYYY-MM-DD HH24:MI:00') AS colmun_koma_timestamp 
FROM test_table
GROUP BY colmun_koma_timestamp;

SELECT

TO_TIMESTAMP((TO_CHAR(column_timestamp, 'YYYY-MM-DD HH24') || ':' || TO_CHAR(

TRUNC( EXTRACT('minute' FROM column_timestamp) / 30 ) * 30 , 'FM09'

)) , 'YYYY-MM-DD HH24:MI:00') AS colmun_koma_timestamp

FROM test_table

GROUP BY colmun_koma_timestamp;

この部分だけWITH句で頭に切り出して使うことが多い気がします。

まとめ

最初PostgreSQLを使い始めたときに、Date型とTime型をどうすれば、TIMESTAMP型に変換できるのか分からず右往左往していたのが懐かしいです。システムによっては、時間に依存するデータをメインで使うこともあるので、知っておくと便利だと思います。

他にも便利な書き方があれば、ぜひご教授ください。