PostgreSQLで日時を扱うTIPS7選！ 日付の変換・加算・結合

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

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

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

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

Date型 + Time型 = TIMESTAMP型

SELECT column_date + column_time FROM table;

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

日時を加算、減算をする interval

(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;

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

CAST(TIMESTAMP AS DATE,TIME)

SELECT CAST(column_timestamp AS DATE) FROM table; // 日付
SELECT CAST(column_timestamp AS TIME) FROM table; // 時刻

文字列をTIMESTAMP型に変換する

TO_TIMESTAMP(文字列, ‘YYYY-MM-DD HH24:MI:00’)

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

文字列を日付型に変換する

TO_DATE(文字列, ‘YYYY-MM-DD’)

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

N分ごとに集計

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

 
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型に変換できるのか分からず右往左往していたのが懐かしいです。システムによっては、時間に依存するデータをメインで使うこともあるので、知っておくと便利だと思います。
 
他にも便利な書き方があれば、ぜひご教授ください。
 
 
 
 
《関連記事》

技術

2022.10.14(Fri)

PostgreSQLで連番を作るGENERATE_SERIES関数を使ってみた

#SQL

技術

2022.8.24(Wed)

PostgreSQLで検索結果を結合する！ INTERSECTとEXCEPTを使ってみた

#SQL

技術

2022.7.20(Wed)

PostgreSQLで検索結果を結合する！ UNIONを使ってみた

#SQL