DB2 9.7では？

新しいDB2 9.7では、日付型やタイムスタンプ型を自由にフォーマットできるようにTO_CHAR関数が拡張されています。
DB2 9.7で実装された新しい機能をご覧下さい。

上に戻る

はじめに

この記事は、DB2 UDBの初心者で、日付と時刻を操作する方法を理解したい読者を対象にしています。他のデータベースを扱った経験のある方は、DB2 UDBで簡単に操作できると感じるでしょう。

上に戻る

基礎

SQLを使って現在の日付、時刻、タイムスタンプを取得するには、該当するDB2レジスターを参照してください。

                  SELECT current date FROM sysibm.sysdummy1  SELECT current time FROM sysibm.sysdummy1  SELECT current timestamp FROM sysibm.sysdummy1   

sysibm.sysdummy1テーブルは、上記のようなDB2レジスターの値を検出するのに使用できる特殊なメモリー内テーブルです。VALUESキーワードを使って、レジスターまたは式を評価することもできます。たとえば、DB2コマンド行プロセッサー（CLP）から、次のSQLステートメントで、同様の情報が表示されます。

                  VALUES current date  VALUES current time  VALUES current timestamp  

残りの例については、SELECT ... FROM sysibm.sysdummy1 を反復しないで、またはVALUESを使わないで、単純に関数または式を提供します。

GMT/CUTに調整後の現在の時刻または現在のタイムスタンプを取得するには、現在の時刻またはタイムスタンプから現在のタイムゾーン・レジスターを差し引いてください。

                  current time - current timezone  current timestamp - current timezone  

日付、時刻、タイムスタンプを入力すると、（該当する場合）該当する関数を使って、年、月、日、時間、分、秒、マイクロ秒部分を独立して取り出すことができます。

                  YEAR (current timestamp)  MONTH (current timestamp)  DAY (current timestamp)  HOUR (current timestamp)  MINUTE (current timestamp)  SECOND (current timestamp)  MICROSECOND (current timestamp)   

タイムスタンプから独立して日付時刻を取り出すのも、非常に簡単です。

                  DATE (current timestamp)  TIME (current timestamp)   

英語（これ以上のタームはありません）を使って、日付時刻の計算を実行することもできます。

どのように私は彼女を愛して私のガールフレンドを伝えることができます

                  current date + 1 YEAR  current date + 3 YEARS + 2 MONTHS + 15 DAYS  current time + 5 HOURS - 3 MINUTES + 10 SECONDS   

2つの日付間の日数を計算するには、次のように日付の減算ができます。

                  days (current date) - days (date('1999-10-22'))   

下記は、マイクロ秒部分をゼロにリセットして、現在のタイムスタンプを取得する方法の例です。

                  CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS   

日付時刻の値を他のテキストと連結したいときは、最初に、値を文字列に変換する必要があります。これを実行するには、単純にCHAR()関数を使用することができます。

                  char(current date)  char(current time)  char(current date + 12 hours)   

文字列を日付時刻の値に変換するには、次の関数を使用することができます。

                  TIMESTAMP ('2002-10-20-12.00.00.000000')  TIMESTAMP ('2002-10-20 12:00:00')          DATE ('2002-10-20')          DATE ('10/20/2002')          TIME ('12:00:00')          TIME ('12.00.00')   

TIMESTAMP()、DATE()、TIME()関数に許容される形式は、このほかにも複数あります。上記の形式はあくまでも参考例です。使える形式を探すのは、読者への宿題とします。

                              SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1;   

上に戻る

Date関数

どのように女の子とフレンチキスへ

2つのタイムスタンプ間の差を知りたいときがあります。このため、DB2には、TIMESTAMPDIFF()と呼ばれるビルトインの関数が用意されています。ただし、閏年は計算に入れず、1ヵ月は常に30日としていますので、返される値は近似値です。下記は、2つの日付間時間差の近似値を確認する方法の例です。

                  timestampdiff (, char(        timestamp('2002-11-30-00.00.00')-        timestamp('2002-11-08-00.00.00')))    

に次の値のいずれかを使って、結果の時間単位を指定します。

• 1 = 秒小数部
• 2 = 秒
• 4 = 分
• 8 = 時間
• 16 = 日
• 32 = 週
• 64 = 月
• 128 = 四半期
• 256 = 年

timestampdiff()を使用するのは、日付が離れているときよりも、近いときの方が正確です。もっと正確な計算が必要なときは、次の式を使って、時間差（秒）を確認することができます。

                  (DAYS(t1) - DAYS(t2)) * 86400 +  (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))   

便宜のために、上記のSQLユーザー定義関数を作成することもできます。

                  CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)  RETURNS INT  RETURN (  (DAYS(t1) - DAYS(t2)) * 86400 +  (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))  ) datetime ISO blocking all grant public   

（DBNAMEとISOには、実際のデータベース名と希望する日付形式をそれぞれ挿入してください。）

これで、データベースはISO日付形式を使用します。

                  values current date  1  ----------  2003-05-30      1 record(s) selected.   

上に戻る

カスタム日付/時刻形式の設定

前の例では、ローカライズされた形式で、DB2の日付表示を変更する方法について説明しました。では、'yyyymmdd'など、カスタム形式で表示したいときはどうすればよいのでしょうか。その最善の方法は、カスタム形式設定用の関数を書くことです。

女性は愛から落ちる理由

                  create function ts_fmt(TS timestamp, fmt varchar(20))  returns varchar(50)  return  with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as  (      select      substr( digits (day(TS)),9),      substr( digits (month(TS)),9) ,      rtrim(char(year(TS))) ,      substr( digits (hour(TS)),9),      substr( digits (minute(TS)),9),      substr( digits (second(TS)),9),      rtrim(char(microsecond(TS)))      from sysibm.sysdummy1      )  select  case fmt      when 'yyyymmdd'          then yyyy || mm || dd      when 'mm/dd/yyyy'          then mm || '/' || dd || '/' || yyyy      when 'yyyy/dd/mm hh:mi:ss'          then yyyy || '/' || mm || '/' || dd || ' ' ||                  hh || ':' || mi || ':' || ss      when 'nnnnnn'          then nnnnnn      else          'date format ' || coalesce(fmt,'  ') ||           ' not recognized.'      end  from tmp    

関数コードは、最初は複雑に見えるかもしれませんが、つぶさに見ると、実際にはまったく簡素でエレガントであることがわかります。最初に、timestamp（最初の入力パラメーター）を個別コンポーネントに分解するのに、共通表式（CTE）を使用します。そのあと、提供された形式（2つ目の入力パラメーター）をチェックし、要求された形式と構成部分を使って、タイムスタンプを再組立てします。

この関数は、柔軟性にも優れています。別のパターンを追加するには、期待する形式に対応する新しいWHENクローズを追加するだけです。予期しないパターンに遭遇したときは、エラー・メッセージが返されます。

使用例：

                  values ts_fmt(current timestamp,'yyyymmdd')   '20030818'  values ts_fmt(current timestamp,'asa')    'date format asa not recognized.'  

上に戻る

要約

この記事で取り上げた例は、日付と時刻に関して、筆者がよく目、耳にする最も一般的な質問に対する答えです。みなさんからのフィードバックでもっと例がほしいという声が大きかったら、この記事を更新することにします（事実、読者のみなさんのおかげで、すでに3回アップデートしました）。

謝辞

Bill Wilkins, DB2 Partner Enablement

Randy Talsma

特記事項

この記事には、サンプル・コードが含まれています。IBMは、このサンプル・コードを使用する非排他的、ロイヤルティフリーのライセンスを読者（「ライセンシー」）に提供します。ただし、サンプル・コードは現状のまま提供され、商品性、目的適合性、非権利侵害の黙示保証を含めて、明示、黙示を問わず、一切、保証はありません。IBMとそのライセンサーは、ソフトウェアの使用の結果、ライセンシーに生じた被害について一切責任を負いません。いかなる場合にも、IBMとそのライセンサーは、収入、利益もしくはデータの喪失に対して一切の責任を負わず、また、原因を問わず、かつ、責任の理論にかかわらず、その損害の可能性についてIBMに通知されていた場合も含めて、ソフトウェアの使用または使用不能に起因して発生し� ��、直接的、間接的、特別、結果的、付帯的、もしくは懲罰的損害に対して、一切の責任を負いません。

参考文献

著者について

この記事を評価する

コメント

上に戻る