日付型


処理しにくい最もたるものはDATE型でしょう。 Oracleは、常に時刻までを含むDATE型の1つしかないので、 特に厄介だと言えます。

時間の成分を排除するためにTRUNC関数を使うのは、 一般的な方法になっています。実際には、Oracleには純粋な DATE型は存在しないので、この関数は時間成分を削除 してしまうわけではなく、その代わり時刻を0時0分に設定します。 検索時に時間成分を無視するには、比較の両辺で TRUNC関数を使用します。前日の売り上げを知りたい時は 以下のようにします。

SELECT ...
  FROM sales
 WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

これは完璧に有効で正しい文ですが、SALE_DATEに 作られたインデックスを正しく使うことはできません。この理由は、 UPPERLOWERを使った 大文字・小文字を区別する検索」で 説明されています。関数は、データベースにとってブラックボックス なので、TRUNC(sale_date)SALE_DATEは 全く異なるものとして扱われてしまうのです。

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

これには、関数インデックスと いう簡単な解決策があります。

CREATE INDEX index_name
          ON sales (TRUNC(sale_date))

しかしこれだと、where句で常に TRUNC(sale_date)を使う必要があります。 TRUNCを使ったり使わなかったりする場合は、2つの インデックスが必要になってしまうのです!

純粋な日付型を持っているデータベースでも、より長い期間を 検索する時には同じ問題があります。以下はMySQLのクエリの例です。

SELECT ...
  FROM sales
 WHERE DATE_FORMAT(sale_date, "%Y-%M")
     = DATE_FORMAT(now()    , "%Y-%M")

これは年と月のみを含んだ日付のフォーマットを指定して いて、完全に正しいクエリなのですが、前と同じ問題があります。MySQLには 関数インデックスの機能がないので、上述の解決策は使えません。

代替策として、明確な範囲条件を使います。これは、全てのデータ ベースで使える一般的な解決策です。

SELECT ...
  FROM sales
 WHERE sale_date BETWEEN quarter_begin(?) 
                     AND quarter_end(?)

ここまでに出た宿題を済ませているなら、このパターンが 42歳の従業員全てを求める問題と 同じであることに気付いたでしょう。

SALE_DATEに対するインデックスで、このクエリを 最適化できます。QUARTER_BEGINQUARTER_END関数で、上限と下限を計算できます。 between演算子は常に 上限と下限を含むので、この計算は少々複雑になります。 SALE_DATEが時間成分を含んでいるなら、 QUARTER_END関数は、次の四半期初日の直前の時刻を返さ なくてはなりません。このロジックが関数に隠されています。

以下の例は、各種データベースでの QUARTER_BEGINQUARTER_ENDの 実装です。

DB2
CREATE FUNCTION quarter_begin(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q');
CREATE FUNCTION quarter_end(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q') + 3 MONTHS - 1 SECOND;
MySQL
CREATE FUNCTION quarter_begin(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT
       (
         CONCAT
         ( CONVERT(YEAR(dt),CHAR(4))
         , '-'
         , CONVERT(QUARTER(dt)*3-2,CHAR(2))
         , '-01'
         )
       , datetime
       );
CREATE FUNCTION quarter_end(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD
       ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH )
       , INTERVAL -1 MICROSECOND);
Oracle
CREATE FUNCTION quarter_begin(dt IN DATE) 
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END;
/
CREATE FUNCTION quarter_end(dt IN DATE) 
RETURN DATE
AS
BEGIN
   -- the Oracle DATE type has seconds resolution
   -- subtract one second from the first 
   -- day of the following quarter
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') 
        - (1/(24*60*60));
END;
/
PostgreSQL
CREATE FUNCTION quarter_begin(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
    RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION quarter_end(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
   RETURN   date_trunc('quarter', dt) 
          + interval '3 month'
          - interval '1 microsecond';
END;
$$ LANGUAGE plpgsql;
SQL Server
CREATE FUNCTION quarter_begin (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0)  
END;
CREATE FUNCTION quarter_end (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD
         ( ms
         , -3 
         , DATEADD(mm, 3, dbo.quarter_begin(@dt))
         );
END;

異なる期間に対しても、同様の補助的な関数を使うことができます。 多くは、上の例よりも複雑にはならないでしょう。特に、 betweenの代わりに以上(>=)や 未満(<)を使える時はそうです。もちろん、上限や下限の 日付をアプリケーション内で計算しても構いません。

Tweet this tip

ヒント

連続する期間を表す時は、明確な範囲条件を 指定したクエリにしましょう。1日だけを指定する場合も同じです。 Oracleの場合は以下のようになります。

    sale_date >= TRUNC(sysdate)
AND sale_date <  TRUNC(sysdate + INTERVAL '1' DAY)

もう1つ処理しにくい例としてよくあるのが、次のPostgreSQLの例のように、 日付を文字列として比較してしまう場合です。

SELECT ...
  FROM sales
 WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'

ここでも問題になるのは、SALE_DATEの変換です。 このような条件は、数値と文字列のような異なる型をデータベースに 渡せないという思い込みから作られます。ところが、バインドパラメータは、 全てのデータ型をサポートしています。例えば、java.util.Date オブジェクトをバインドパラメータに使うこともできるのです。これも、 バインドパラメータの利点の1つです。

もしそうできない場合は、テーブルの列の代わりに検索語の方を 変換してしまいましょう。

SELECT ...
  FROM sales
 WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')

このクエリは、SALE_DATEのインデックスをちゃんと 使うことができます。しかもこの場合、入力文字列を1度しか変換しません。 前の文だと、検索語との比較を行う前に、テーブルに保存された全ての日付を 変換する必要がありました。

バインドパラメータを使っても、比較式の違うところを変換する ようにしても、SALE_DATEが時間成分を含んでいる場合は、 簡単にバグが発生してしまいます。このような場合には、明示的な 範囲条件を使わなくてはなりません。

SELECT ...
  FROM sales
 WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD') 
   AND sale_date <  TO_DATE('1970-01-01', 'YYYY-MM-DD') 
                  + INTERVAL '1' DAY

日付を比較する際は、常に明確な範囲条件を使うよう心がけましょう。

日付型に対するLIKE

以下の例は、特に注意すべき間違いです。

sale_date LIKE SYSDATE

これは、関数を使用していないので処理しにくくはないように 見えます。

しかし、LIKE 演算子は必ず文字列比較を 行います。データベースによっては、エラーを出力するものも ありますし、両辺とも暗黙の型変換が行われることもあります。 Oracleの実行計画の「Predicate Information」は以下のように なります。

filter( INTERNAL_FUNCTION(SALE_DATE)
   LIKE TO_CHAR(SYSDATE@!))

INTERNAL_FUNCTION 関数は、SALE_DATE列の型を変換します。その副作用として、 他の関数と同じようにDATE_COLUMNの インデックスを使わなくなってしまいます。

Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。