by Hayato Matsuura.

日付型


処理しにくい最もたるものは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は 全く異なるものとして扱われてしまうのです。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

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

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の代わりに以上(>=)や 未満(<)を使える時はそうです。もちろん、上限や下限の 日付をアプリケーション内で計算しても構いません。

ヒント

連続する期間を表す時は、明確な範囲条件を 指定したクエリにしましょう。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の インデックスを使わなくなってしまいます。

著者について

Markus Winandの写真

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

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license