DB2の基礎： 日付と時刻で遊ぶ

Paul Yip
IBM Business Partner Enablement
IBMトロント研究所

2003年8月
重要： この記事を読むまえに、特記事項に目を通してください。 この記事は、IBM^® DB2^® Universal Database^TM for Linux、UNIX^®、Windows^®を対象に執筆されています。

コンテンツ はじめに 基礎 Date関数 日付形式の変更 カスタム日付/時刻形式の設定 要約 著者について

はじめに
この記事は、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; 

====== 
05/24/0006

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

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

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

• 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)) 
) 
@ 

特定の年が閏年であるかどうかを確認する必要があるときは、次のSQL関数が便利です。この関数を作成して1年間の日数を確認することができます。

CREATE FUNCTION daysinyear(yr INT) 
RETURNS INT 
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE 
        CASE (mod(yr, 4))   WHEN 0 THEN 
        CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END 
        ELSE 365 END 
	END)@ 

最後に、下記は、日付操作用のビルトイン関数のチャートです。詳細な参照用ではなく、自分のニーズに合いそうな関数を手早く見付けることが目的です。これらの関数について詳しくは、『SQL Reference』（SQLリファレンス）を参照してください。

日付形式の変更
よく耳にする一般的質問は、日付の表示の仕方についてです。日付に使用されるデフォルトの形式は、データベースのテリトリー・コードにより決定されます（データベース作成時に指定可能です）。たとえば、筆者のデータベースは、territory=USを使って作成しました。したがって、日付形式は、次のように表示されます。

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

つまり、形式は、MM/DD/YYYYです。形式を変更したい場合、db2ユーティリティー・パッケージのコレクションをバインドして、異なる日付形式を使用することができます。サポートされている形式は、次のとおりです。

Windows上でデフォルトの形式（YYYY-MM-DD）をISOに変更するには、次の手順を実行してください。

1. コマンド行で、現在のディレクトリーをsqllib\bndに変更してください。
   
   例：
   On Windows: c:\program files\IBM\sqllib\bnd
   On UNIX:/home/db2inst1/sqllib/bnd
   
   
2. SYSADM権限をもつユーザーとしてオペレーティング・システム・シェルからデータベースに接続してください。
   
   

   db2 connect to DBNAME db2 bind @db2ubind.lst 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,' <null> ') ||  
        ' not recognized.' 
    end 
from tmp 
</null>

関数コードは、最初は複雑に見えるかもしれませんが、つぶさに見ると、実際にはまったく簡素でエレガントであることがわかります。最初に、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に通知されていた場合も含めて、ソフトウェアの使用または使用不能に起因して発生した、直接的、間接的、特別、結果的、付帯的、もしくは懲罰的損害に対して、一切の責任を負いません。

著者について

IBM、AIX。DB2およびDB2 Universal Databaseは、米国、その他の国または両方において、IBM Corporationの商標または登録商標です。
Windowsは、米国、その他の国または両方においてMicrosoft Corporationの登録商標です。
UNIXは、米国その他の国において、 The Open Groupの登録商標です。
その他の会社名、製品およびサービス名は、一般に所有各社の商標です。
IBMの著作権および商標情報

上に戻る