hoge256ブログ

前に JavaScript や PHP の日付関連処理のまとめを書いたところ、結構アクセスしてくる人が多かったので今度は 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です。
ただ、エラー処理や例外処理等は考慮されていませんので、使うときは適宜調整するといいかもしれないです。

カテゴリー: 未分類 | コメントはまだありません »