
こんにちは、飯塚です。
普段の業務の中で1分値、30分値など、時間とともに変わるデータを扱うことが多いです。今回は日時の加算や、文字列⇔日付型の変換など、時間に依存するテーブルを扱う場合に、知っておくと便利なテクニックを備忘録としてまとめてみました。
PostgreSQLで日時を管理するときの型
はじめてPostgreSQLを触ったときに、時間を扱う型の種類を知らなくて混乱したのでまとめました。基本この4つだけ知ってればイケるはず。
型 | 例 |
---|---|
TIMESTAMP型 | 2019-03-01 10:00:00 |
Date型 | 2019-03-01 |
Time型 | 10:00:00 |
文字列 | Mar 1, 20190301, …etc |
日付(Date型)と時刻(TIME型)の結合
Date型 + Time型 = TIMESTAMP型
1 |
SELECT column_date + column_time FROM table; |
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’
1 |
SELECT column_time + interval '30 minutes' FROM table; |
TIMESTAMP型、DATE型、TIME型で使用可能です。intervalで加減算できる時間の単位(hour, minutesなど)は以下参照。
日付 / 時刻関数と演算子
開始時間+経過時間から終了時間を計算
interval ‘N xxxxxx’ * 経過時間
1 2 |
SELECT column_timestamp_from + interval '1 minutes' * column_duration AS end_datetime FROM table; |
時間幅を持っているテーブルに有効です。
TIMESTAMP型から日付、時刻を取り出す
1 |
SELECT CAST(column_timestamp AS DATE) FROM table; |
よく文字列に一旦変換するSQLを見かけますが、「CAST ~ AS XXX」の書き方が推奨されています。
文字列→TIMESTAMP
1 |
SELECT TO_TIMESTAMP(column_timestamp_str, 'YYYY-MM-DD HH24:MI:00') FROM table; |
直観的でわかりやすいですね!
文字列→日付
1 |
SELECT TO_DATE(column_date_str, 'YYYY-MM-DD') FROM table; |
これもそのままという感じ。
N分ごとに集計
15分単位のデータを30分単位に集計するイメージはこんな感じです。
元データ | 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は以下です。
1 2 3 4 5 6 |
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型に変換できるのか分からず右往左往していたのが懐かしいです。システムによっては、時間に依存するデータをメインで使うこともあるので、知っておくと便利だと思います。
他にも便利な書き方があれば、ぜひご教授ください。

