AMG Solution

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

こんにちは、飯塚です。
 
普段の業務の中で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型

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

日時加算、減算をする interval

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

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

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

interval ‘N xxxxxx’ * 経過時間

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

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

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

文字列→TIMESTAMP

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

文字列→日付

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

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は以下です。

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

まとめ

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

IIZUKA'S BLOG

飯塚暉の記事

飯塚暉の記事の最新情報をお届けいたします。

SAME CATEGORY BLOG

この記事と同様のカテゴリー記事

新卒採用
はじめました。
LOADING