PostgreSQL による日付・時刻・時間の計算・演算のまとめ

前に JavaScriptPHP の日付関連処理のまとめを書いたところ、結構アクセスしてくる人が多かったので今度は PostgreSQL に関する日付関連処理についてまとめてみました。

SQLの場合いわゆる標準が決まっていますので、ここで述べている例は、なるべく標準に準拠した形になっています。なので、昔から PostgreSQL で使われていたやり方とは一部違うかもしれないです。

日付の単位について

基本的なことですが、一応まとめておきます。

1秒は1000ミリ秒
1分は60秒
1時間は60分
よって
1時間=60分=3600秒=3600000ミリ秒
1日=24時間=1440分=86400秒=86400000ミリ秒

日付/時刻のパース

日付/時刻を表したある書式の文字列から PostgreSQL の TIMESTAMP 型に変換します。

SELECT CAST('2008-04-14 22:45:30' AS TIMESTAMP);

現在の日付/時刻を秒数で求める

現在の日付/時刻をいわゆる UNIX タイムスタンプ値で求めます。

-- 現在を秒数で取得
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP);

-- 日時の文字列から秒数に変換
SELECT EXTRACT(EPOCH FROM CAST('2007-08-25 13:15:30' AS TIMESTAMP));

-- 日の文字列から秒数に変換
SELECT EXTRACT(EPOCH FROM CAST('2007-08-01' AS DATE));

n日後、n日前の日付を求める

今日から1週間後は何月何日?とか、10日前は何月何日?みたいな計算を行う場合に使います。

-- 7日後を求める
SELECT CAST('2008-04-25' AS DATE) + CAST('7 days' AS INTERVAL);

-- 14日前を求める
SELECT CAST('2008-04-25' AS DATE) - CAST('14 days' AS INTERVAL);

任意の年月の月末日を求める

月末日は求めたい月の翌月1日から1日を引くことで算出できます。

--4月の末日ならば5月1日から1日を引く
SELECT CAST('2008-05-01' AS DATE) - CAST('1 day' AS INTERVAL);

nヶ月後、nヶ月前の日付を求める

nヶ月後とは単純にn * 30日ではないことに注意してください。
例えば、1月10日の1ヶ月後は2月10日ですが、1月31日の1ヶ月後は2月28日(うるう年の場合2月29日)になるという考え方です。

-- 2ヶ月後を求める
SELECT CAST('2008-07-31' AS DATE) + CAST('2 months' AS INTERVAL);
-- うるう年の1月31日の1ヶ月後を求める
SELECT CAST('2008-01-31' AS DATE) + CAST('1 month' AS INTERVAL);

日付の比較を行う

日付の比較です。SELECT しているデータはダミーです。

--日付の比較を行う
SELECT true WHERE CAST('2008-04-30' AS DATE) > CAST('2008-01-31' AS DATE);
SELECT true WHERE CAST('2008-04-10' AS DATE) > CAST('2008-04-20' AS DATE);

2つの日付の差(何日間あるか)を求める

2つの日付の差を求めます。n年n月n日まで、あとn日。みたいな感じでカウントダウンなんかにも使えます。

--2つの日付の差(何日間あるか)を求める
SELECT CAST('2008-04-30' AS DATE) - CAST('2008-01-31' AS DATE);
SELECT CAST('2008-04-10' AS DATE) - CAST('2008-04-20' AS DATE);

任意の日付の曜日を取得

任意の日付の曜日を取得します。日本語の曜日名を取得するのに、配列を使っています。

SELECT (ARRAY['日','月','火','水','木','金','土'])[EXTRACT(DOW FROM CAST('2008-04-21' AS DATE)) + 1];

以上、とりあえず、日付・時刻関連の計算のまとめでした。

説明では、分かりやすくするためテーブルとカラムを指定せずに、文字列の日付を直接記述していますが、CAST(‘2008-04-20’ AS DATE) みたいな記述のところは DATE 型のカラム。CAST(‘2007-08-25 13:15:30’ AS TIMESTAMP) みたいな記述のところは TIMESTAMP型のカラムに置き換えてもらって大丈夫です。

正直、ストアドプロシージャを使えば、データベース側でかなりいろいろ出来てしまうのですが、とりあえず、それはまた今度にします。

上記のサンプルコードは自由に使っていただいてOKです。
ただ、エラー処理や例外処理等は考慮されていませんので、使うときは適宜調整するといいかもしれないです。

Twitterでも情報配信中!



コメント(2)


PostgreSQLでカレンダー

 たまにはまじめに技術系の話を。PostgreSQLです。

 仕事で必要に迫られたので、いろいろ試行錯誤の結果こんなことになったとメモがてら。興味のない方はスルーよろしく。
 関数…


kensanmail

作業日を出してるのに「休みの日も表示しろ」というわがままな注文が来て、カレンダーを主体に日付が一致した作業履歴を拾えばできるとこまでは考えていたのですが具体的にカレンダーをYYYY/MMを指定して発生させるSql構文を作るのにこのページを参考にさせていただきました。
以下 2012/02 のところは外から変数で与えます。
ありがとうございました。

Select
to_char(tm,’YYYY/MM/DD’) as 日付
From
Generate_series(Cast(‘2012/02’|| ‘/01’ as Date), Cast(‘2012/02’||’/01’ as Date)+CAST(‘1 month’ AS INTERVAL)-CAST(‘1 day’ AS INTERVAL), interval ‘1 day’) AS T(tm)
;


コメントを残す

メールアドレスが公開されることはありません。



※コメントは承認制です。承認されるまで表示されません。