PostgreSQL - DATEDIFF - 秒、日、月、週などの日時の差
PostgreSQL では、さまざまな日時式またはユーザー定義の DATEDIFF 関数 (UDF) を使用して、2 つの日時値の差を秒、分、時、日、週、月、年単位で計算できます。
概要
PostgreSQL は、次のような DATEDIFF 関数を提供しません。SQLサーバ日付の差分ですが、さまざまな式や UDF を使用して同じ結果を得ることができます。
| SQLサーバそしてサイベース | PostgreSQL | |
| 年 | DATEDIFF(yy, 開始, 終了) | DATE_PART('年'、終了) - DATE_PART('年'、開始) |
| 月 | DATEDIFF(mm、開始、終了) | years_diff * 12 + (DATE_PART('月', 終了) - DATE_PART('月', 開始)) |
| 日々 | DATEDIFF(dd, 開始, 終了) | DATE_PART('日', 終了 - 開始) |
| 週間 | DATEDIFF(週、開始、終了) | TRUNC(DATE_PART('日', 終了 - 開始)/7) |
| 時間 | DATEDIFF(hh、開始、終了) | days_diff * 24 + DATE_PART('時間', 終了 - 開始 ) |
| 分 | DATEDIFF(mi、開始、終了) | hours_diff * 60 + DATE_PART('分', 終了 - 開始 ) |
| 秒 | DATEDIFF(ss, 開始, 終了) | minutes_diff * 60 + DATE_PART('分', 終了 - 開始 ) |
バージョン：PostgreSQL9.1
PostgreSQL - 年単位の日付の違い
考慮するSQL2 つの日付の差を年単位で計算するサーバー関数:
SQLサーバ:
-- 2011 年 10 月 2 日と 2012 年 1 月 1 日の年の差 選択する日付の差分(年、 「2011-10-02」、 「2012-01-01」);-- 結果: 1
ご了承くださいSQL日付間の間隔が 3 か月しかないにもかかわらず、サーバーの DATEDIFF 関数は 1 年を返しました。
SQLサーバーはカウントされません満杯日付間の経過年数を計算すると、日付間の差が計算されます。年式の部分のみ。
PostgreSQL では、日付から年の部分を取得して減算できます。
PostgreSQL:
-- 2011 年 10 月 2 日と 2012 年 1 月 1 日の年の差 選択するDATE_PART('年'、 「2012-01-01」::日付) -DATE_PART('年'、 「2011-10-02」::日付);-- 結果: 1
PostgreSQL - 月単位の日付の違い
考慮するSQL月単位の 2 つの日付の差を計算するサーバー関数:
SQLサーバ:
-- 2011 年 10 月 2 日と 2012 年 1 月 1 日の月数の差 選択する日付の差分(月、 「2011-10-02」、 「2012-01-01」);-- 結果: 3
PostgreSQL では、年の差を取得し、12 を掛けて、次の差を加算できます。月の部分それはマイナスになる可能性があります。
PostgreSQL:
-- 2011 年 10 月 2 日と 2012 年 1 月 1 日の月数の差 選択する (DATE_PART('年'、 「2012-01-01」::日付) -DATE_PART('年'、 「2011-10-02」::日付)) * 12 + (DATE_PART('月'、 「2012-01-01」::日付) -DATE_PART('月'、 「2011-10-02」::日付));-- 結果: 3
PostgreSQL - 日付の差 (日数)
考慮するSQL2 つの日付の差を日数で計算するサーバー関数:
SQLサーバ:
-- 2011 年 12 月 29 日 23:00 と 2011 年 12 月 31 日 01:00 の日数の差 選択する日付の差分(日、 「2011-12-29 23:00:00」、 「2011-12-31 01:00:00」);-- 結果: 2
日時値の間には 1 日と 2 時間しかありませんが、DATEDIFF は 2 日を返したことに注意してください。
PostgreSQL では、ある日時値 (TIMESTAMP、DATE、または TIME データ型) を別の日時値から減算すると、「」という形式の INTERVAL 値が得られます。DD日々ひ:み:ss”。
選択する 「2011-12-31 01:00:00」::タイムスタンプ - 「2011-12-29 23:00:00」::タイムスタンプ;-- 結果: 「1 日 02:00:00」 選択する 「2011-12-31 01:00:00」::タイムスタンプ - 「2010-09-17 23:00:00」::タイムスタンプ;-- 結果: 「469 日 02:00:00」
したがって、DATE_PART 関数を使用して日数を抽出できますが、返されるのは日数です。満杯日付間の日数。
PostgreSQL:
-- 2011 年 12 月 29 日 23:00 と 2011 年 12 月 31 日 01:00 の日数の差 選択するDATE_PART('日'、 「2011-12-31 01:00:00」::タイムスタンプ - 「2011-12-29 23:00:00」::タイムスタンプ);-- 結果: 1
PostgreSQL - 週単位の日付の差
考慮するSQL2 つの日付の差を週単位で計算するサーバー関数:
SQLサーバ:
-- 2011 年 12 月 22 日と 2011 年 12 月 31 日の週数の差 選択する日付の差分(週、 「2011-12-22」、 「2011-12-31」);-- 結果: 1
DATEDIFF は次の数を返します。満杯日時値間の週数。
PostgreSQL では、式を使用して日数 (上記を参照) を定義し、それを 7 で割ることができます。除算後の小数部分を削除するには、TRUNC が必要です。
PostgreSQL:
-- 2011 年 12 月 22 日と 2011 年 12 月 31 日の週数の差 選択するトランク(DATE_PART('日'、 「2011-12-31」::タイムスタンプ - 「2011-12-22」::タイムスタンプ)/7);-- 結果: 1
PostgreSQL - 日時の差 (時間単位)
考慮するSQL2 つの日時値の差を時間単位で計算するサーバー関数:
SQLサーバ:
-- 2011 年 12 月 30 日 08:55 と 2011 年 12 月 30 日 9:05 の週数の差 選択する日付の差分(時間、 「2011-12-30 08:55」、 「2011-12-30 09:05」);-- 結果: 1
日時値の間にはわずか 10 分の違いがあるにもかかわらず、DATEDIFF は 1 時間を返したことに注意してください。
PostgreSQL では、式を使用して日数 (上記を参照) を定義し、24 倍し、その差を時間に加算できます。
PostgreSQL:
-- 2011 年 12 月 30 日 08:55 と 2011 年 12 月 30 日 9:05 の週数の差 選択するDATE_PART('日'、 「2011-12-30 08:55」::タイムスタンプ - 「2011-12-30 09:05」::タイムスタンプ) * 24 +DATE_PART('時間'、 「2011-12-30 08:55」::タイムスタンプ - 「2011-12-30 09:05」::タイムスタンプ);-- 結果: 0
この PostreSQL 式は、次の数を返すことに注意してください。満杯日時値の間で経過した時間。
PostgreSQL - 日付時刻の差 (分単位)
考慮するSQL2 つの日時値の差を分単位で計算するサーバー関数:
SQLサーバ:
-- 2011 年 12 月 30 日 08:54:55 と 2011 年 12 月 30 日 08:56:10 の差 (分単位) 選択する日付の差分(分、 「2011-12-30 08:54:55」、 「2011-12-30 08:56:10」);-- 結果: 2 -- 時間のみ 選択する日付の差分(分、 「08:54:55」、 「08:56:10」);-- 結果: 2
日時値の間には 1 分 15 秒しかないにもかかわらず、DATEDIFF は 2 分を返したことに注意してください。
PostgreSQL では、式を使用して時間数 (上記を参照) を定義し、60 倍し、差分を加算できます。
PostgreSQL:
-- 2011 年 12 月 30 日 08:54:55 と 2011 年 12 月 30 日 08:56:10 の差 (分単位) 選択する (DATE_PART('日'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ) * 24 +DATE_PART('時間'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ)) * 60 +DATE_PART('分'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ);-- 結果: 1 -- 時間のみ 選択するDATE_PART('時間'、 「08:56:10」::時間 - 「08:54:55」::時間) * 60 +DATE_PART('分'、 「08:56:10」::時間 - 「08:54:55」::時間);-- 結果: 1
これらの PostreSQL 式は、次の数を返すことに注意してください。満杯日時値の間で経過した分。
PostgreSQL - 秒単位の日時差
考慮するSQL2 つの日時値の差を秒単位で計算するサーバー関数:
SQLサーバ:
-- 2011 年 12 月 30 日 08:54:55 と 2011 年 12 月 30 日 08:56:10 の秒数の差 選択する日付の差分(2番、 「2011-12-30 08:54:55」、 「2011-12-30 08:56:10」);-- 結果: 75 -- 時間のみ 選択する日付の差分(2番、 「08:54:55」、 「08:56:10」);-- 結果: 75
PostgreSQL では、式を使用して分数 (上記を参照) を定義し、60 倍し、その差を秒単位で加算することができます。
PostgreSQL:
-- 2011 年 12 月 30 日 08:54:55 と 2011 年 12 月 30 日 08:56:10 の秒数の差 選択する ((DATE_PART('日'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ) * 24 +DATE_PART('時間'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ)) * 60 +DATE_PART('分'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ)) * 60 +DATE_PART('2番'、 「2011-12-30 08:56:10」::タイムスタンプ - 「2011-12-30 08:54:55」::タイムスタンプ);-- 結果: 75 -- 時間のみ 選択する (DATE_PART('時間'、 「08:56:10」::時間 - 「08:54:55」::時間) * 60 +DATE_PART('分'、 「08:56:10」::時間 - 「08:54:55」::時間)) * 60 +DATE_PART('2番'、 「08:56:10」::時間 - 「08:54:55」::時間);-- 結果: 75
PostgreSQL DATEDIFF - ユーザー定義関数 (UDF)
各時間単位の日時の差を計算する別の式のほかに、次のような関数を使用できます。SQLサーバーの DATEDIFF 関数。
PostgreSQL:
作成する または 交換する 関数日付の差分(単位VARCHAR(30)、スタートタイムスタンプ、終わりタイムスタンプ) 戻り値 INT として$$宣言するdiff_interval 間隔;差分INT = 0;年の差INT = 0;始める もし単位で (「ええ」、 「ええ」、 '年'、 'んん'、 「私」、 '月') それから年の差=DATE_PART('年'、終わり) -DATE_PART('年'、スタート);もし単位で (「ええ」、 「ええ」、 '年') それから -- SQL Server は経過した年数を丸ごとカウントしません (年部分間の差異のみ) 戻る年の差;それ以外 -- 終了月が開始月より小さい場合は、その月が差し引かれます 戻る年の差* 12 + (DATE_PART('月'、終わり) -DATE_PART('月'、スタート));終わり もし;終わり もし;-- マイナス演算子は間隔「DDD 日 HH:MI:SS」を返します差分間隔=終わり-開始_t;差分=差分+DATE_PART('日'、差分間隔);もし単位で (「週」、 「ww」、 '週') それから差分=差分/7;戻る差分;終わり もし;もし単位で ('dd'、 「だ」、 '日') それから 戻る差分;終わり もし;差分=差分* 24 +DATE_PART('時間'、差分間隔);もし単位で (「ふーん」、 '時間') それから 戻る差分;終わり もし;差分=差分* 60 +DATE_PART('分'、差分間隔);もし単位で (「み」、 「ん」、 '分') それから 戻る差分;終わり もし;差分=差分* 60 +DATE_PART('2番'、差分間隔);戻る差分;終わり; $$言語plpgsql;
PostgreSQLのDATEDIFF関数の使い方
構文は次のようになりますSQLサーバーは DATEDIFF ですが、PostgreSQL では時間単位 (秒、分などとその省略形) を文字列リテラルとして指定する必要があります。次に例を示します。
-- 2011 年 12 月 30 日 08:54:55 と 2011 年 12 月 30 日 08:56:10 の秒数の差 選択する日付の差分('2番'、 「2011-12-30 08:54:55」::タイムスタンプ、 「2011-12-30 08:56:10」::タイムスタンプ);-- 結果: 75
TIME のみの PostgreSQL DATEDIFF 関数
時間データ型のみで動作する別の関数を使用することもできます。 PostgreSQL は、名前は同じだがパラメータのデータ型が異なるオーバーロードされた関数をサポートします。
作成する または 交換する 関数日付の差分(単位VARCHAR(30)、スタート時間、終わり時間) 戻り値 INT として$$宣言するdiff_interval 間隔;差分INT = 0;始める -- TIME のマイナス演算子は間隔 'HH:MI:SS' を返します差分間隔=終わり-開始_t;差分=DATE_PART('時間'、差分間隔);もし単位で (「ふーん」、 '時間') それから 戻る差分;終わり もし;差分=差分* 60 +DATE_PART('分'、差分間隔);もし単位で (「み」、 「ん」、 '分') それから 戻る差分;終わり もし;差分=差分* 60 +DATE_PART('2番'、差分間隔);戻る差分;終わり; $$言語plpgsql;
たとえば、この関数は次のように呼び出すことができます。
-- 08:54:55 と 08:56:10 の差 (秒) 選択する日付の差分('2番'、 「08:54:55」::時間、 「08:56:10」::時間);-- 結果: 75
リソース
PostgreSQL の記事とリファレンス
SQL Server - DATEDIFF 関数
PostgreSQL 9.1 ドキュメント
マイクロソフトSQLServer 2008 R2 オンライン書籍