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

Paul Yip, Database Consultant, IBM Toronto Lab

Paul Yip氏は、分散プラットフォーム用DB2を開発しているIBMトロント研究所に勤務するデータベース・コンサルタントです。主な仕事は、他のデータベースからDB2へのアプリケーションの移行について企業を支援し、現在のスキルをDB2ワールドに適用する方法について熟練したデータベース管理者を教育することです。DB2に関するいくつかの記事やホワイトペーパーの著者であり、顧客のニーズに対応した執筆を行っています。



2009年 5月 28日 (初版 2003年 8月)

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()関数に許容される形式は、このほかにも複数あります。上記の形式はあくまでも参考例です。使える形式を探すのは、読者への宿題とします。

警告:

Graeme Birchall著『DB2 UDB V8.1 SQL Cookbook』から引用

http://ourworld.compuserve.com/homepages/Graeme_Birchall (PDF,846KB)を参照してください)。

DATE関数でたまたま引用符を付け忘れると、どうなるでしょうか。関数は動作しますが、結果は正確ではありません。

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

Answer:

====== 
05/24/0006

上記の結果で2000年もの違いの理由はなんでしょうか。DATE関数は文字列を入力として取得すると、DB2日付の有効な文字列表現であるとみなして、しかるべく変換します。これに対して、入力が数値であるときは、現年代の開始(つまり0001-01-01)からの日数マイナス1を表しているとみなします。上記照会の入力2001-09-22は、(2001-9)-22=1970日と計算されます。


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リファレンス)を参照してください。

SQL DateおよびTime関数
DAYNAME引数のday部分に該当する曜日名(たとえば、Friday)の入った大文字小文字混成文字列を返します。
DAYOFWEEK1~7の範囲の整数値として引数の曜日を返します。1は日曜日を表します。
DAYOFWEEK_ISO1~7の範囲の整数値として引数の曜日を返します。1は月曜日を表します。
DAYOFYEAR1~366の範囲の整数値として、引数のなかの日(1年のうちの何日目であるか)を返します。
DAYS日付の整数表現を返します。
JULIAN_DAYBC(ユリウス暦開始)から引数で指定した日付値までの日数を表す整数値を返します。
MIDNIGHT_SECONDS午前零時から引数で指定した時間値までの秒数を表す0~86 400の範囲の整数値を返します。
MONTHNAME引数のmonth部分に該当する月名(たとえば、January)の入った大文字小文字混成文字列を返します。
TIMESTAMP_ISO日付、時刻またはタイムスタンプ引数に基づいてタイムスタンプ値を返します。
TIMESTAMP_FORMATキャラクター・テンプレートを使って解釈された文字列からタイムスタンプを返します。
TIMESTAMPDIFF2つのタイムスタンプ間の差に基づいて、最初の引数により定義される型の推定時間差を返します。
TO_CHARキャラクター・テンプレートを使ってフォーマットされたタイムスタンプの文字表現を返します。TO_CHARは、VARCHAR_FORMATの同義語です。
TO_DATEキャラクター・テンプレートを使って解釈された文字列からタイムスタンプを返します。TO_DATEは、TIMESTAMP_FORMATの同義語です。
WEEK1~54の範囲の整数値として、引数の週(1年のうちの何週目か)を返します。1週は日曜日に始まります。
WEEK_ISO1~53の範囲の整数値として、引数の週(1年のうちの何週目か)を返します。

日付形式の変更

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

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

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

DEFテリトリー・コードに対応する日付時刻形式を使用します。
EURIBM標準の欧州日付時刻形式を使用します。
ISO国際標準化機構(International Standards Organization)の日付時刻形式を使用します。
JIS日本工業規格の日付時刻形式を使用します。
LOCデータベースのテリトリー・コードに関連付けされたローカルの日付時刻形式を使用します。
USAIBM標準の米国日付時刻形式を使用します。

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に通知されていた場合も含めて、ソフトウェアの使用または使用不能に起因して発生した、直接的、間接的、特別、結果的、付帯的、もしくは懲罰的損害に対して、一切の責任を負いません。

参考文献

コメント

developerWorks: サイン・イン

必須フィールドは(*)で示されます。


IBM ID が必要ですか?
IBM IDをお忘れですか?


パスワードをお忘れですか?
パスワードの変更

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


お客様が developerWorks に初めてサインインすると、お客様のプロフィールが作成されます。会社名を非表示とする選択を行わない限り、プロフィール内の情報(名前、国/地域や会社名)は公開され、投稿するコンテンツと一緒に表示されますが、いつでもこれらの情報を更新できます。

送信されたすべての情報は安全です。

ディスプレイ・ネームを選択してください



developerWorks に初めてサインインするとプロフィールが作成されますので、その際にディスプレイ・ネームを選択する必要があります。ディスプレイ・ネームは、お客様が developerWorks に投稿するコンテンツと一緒に表示されます。

ディスプレイ・ネームは、3文字から31文字の範囲で指定し、かつ developerWorks コミュニティーでユニークである必要があります。また、プライバシー上の理由でお客様の電子メール・アドレスは使用しないでください。

必須フィールドは(*)で示されます。

3文字から31文字の範囲で指定し

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


送信されたすべての情報は安全です。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Information Management
ArticleID=323662
ArticleTitle=DB2の基礎: 日付と時刻で遊ぶ
publish-date=05282009