
お知らせ
2023.4.6(Thu)
目次
こんにちは、飯塚です。
普段の業務の中で1分値、30分値など、時間とともに変わるデータを扱うことが多いです。今回は日時の加算や、文字列⇔日付型の変換など、時間に依存するテーブルを扱う場合に、知っておくと便利なテクニックを備忘録としてまとめてみました。
はじめてPostgreSQLを触ったときに、時間を扱う型の種類を知らなくて混乱したのでまとめました。基本この4つだけ知ってればイケるはず。
型 | 例 |
---|---|
TIMESTAMP型 | 2019-03-01 10:00:00 |
Date型 | 2019-03-01 |
Time型 | 10:00:00 |
文字列 | Mar 1, 20190301, …etc |
Date型 + Time型 = TIMESTAMP型
SELECT column_date + column_time FROM table;
SELECT * FROM table WHERE column_date + column_time <= '2019/03/01 10:00:00';
(TIMESTAMP, DATE, TIME) + interval ‘N xxxxxx’
SELECT column_time + interval '30 minutes' FROM table;
interval ‘N xxxxxx’ * 経過時間
SELECT column_timestamp_from + interval '1 minutes' * column_duration AS end_datetime FROM table;
SELECT CAST(column_timestamp AS DATE) FROM table;
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;
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は以下です。
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;
最初PostgreSQLを使い始めたときに、Date型とTime型をどうすれば、TIMESTAMP型に変換できるのか分からず右往左往していたのが懐かしいです。システムによっては、時間に依存するデータをメインで使うこともあるので、知っておくと便利だと思います。
他にも便利な書き方があれば、ぜひご教授ください。
《関連記事》