アーキテクチャをスマートに。

株式会社ネオジニア代表。ITアーキテクトとしてのお仕事や考えていることなどをたまに綴っています。(記事の内容は個人の見解に基づくものであり、所属組織を代表するものではありません)

sysdate と current_date のトリビア

あんまり役に立つことじゃないかもしれないですが、ほとんど知られてない事だと思います。

Oracle の sysdate と current_date が違う日付を返すという怪奇現象です。
sysdate と current_date は、どちらも同じシステム日付を取得する関数です。
ところが、これらの関数が返す日付がことなる現象に遭遇しました。
というのも、テストのためにサーバの日付をコロコロ変えてたのですが、データベースリンクを使っていてリンク先のサーバの日付と異なる場合にそんな現象が発生しました。

要するにこういうこと

例えば、DBサーバのシステム日付を 2013/05/01 だったとしましょう。

SQL> SELECT sysdate, current_date FROM dual;

SYSDATE  CURRENT_
-------- --------
13-05-01 13-05-01

SQL>

これは普通ですね。5月1日であってます。

次に、別サーバのDBへデータベースリンクを張ります。名前は「linkdb」としました。
そっちのサーバはシステム日付を 2013/04/01 にしておきます。

SQL> SELECT sysdate, current_date FROM dual@linkdb;

SYSDATE  CURRENT_
-------- --------
13-05-01 13-04-01

SQL>

こうなります。
dualじゃなくても、リンク先のテーブルを見に行くとこうなる場合があります。
じゃあリンク先のテーブルと JOIN とかやったらどうなるのかというと、もうハチャメチャになります。(笑

実験

というわけでいろいろ試してみました。
まず、

-- リンク先DBで
CREATE TABLE test1(dt DATE);  
-- ローカルDBで
CREATE TABLE test2(dt DATE); 

とやって、どちらも適当に同じデータを入れました。

SQL> -- まずはデータ確認
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt;

DT       DT
-------- --------
13-03-31 13-03-31
13-04-01 13-04-01
13-04-02 13-04-02
13-04-30 13-04-30
13-05-01 13-05-01
13-05-02 13-05-02

6行が選択されました。

SQL>
SQL> -- リンク先のカラムに対して大なり小なり
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE a.dt > current_date;

DT       DT
-------- --------
13-05-02 13-05-02           -- current_dateは 13-05-01

SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE a.dt < current_date;

DT       DT
-------- --------
13-03-31 13-03-31
13-04-01 13-04-01           -- current_dateは 13-04-01

SQL>
SQL> -- ローカルのカラムに対して大なり小なり
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE b.dt > current_date;

DT       DT
-------- --------
13-05-02 13-05-02           -- current_dateは 13-05-01

SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE b.dt < current_date;

DT       DT
-------- --------
13-03-31 13-03-31
13-04-01 13-04-01           -- current_dateは 13-04-01

SQL>
SQL> -- truncしてイコールで比較(リンク先)
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE trunc(a.dt) = trunc(current_date);

DT       DT
-------- --------
13-04-01 13-04-01           -- current_dateは 13-04-01

SQL>
SQL> -- truncしてイコールで比較(ローカル)
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE trunc(b.dt) = trunc(current_date);

DT       DT
-------- --------
13-05-01 13-05-01           -- current_dateは 13-05-01

SQL>
SQL> -- to_charしてイコールで比較(リンク先)
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE to_char(a.dt,'YYYYMMDD') = to_char(current_date,'YYYYMMDD');

DT       DT
-------- --------
13-04-01 13-04-01           -- current_dateは 13-04-01

SQL>
SQL> -- to_charしてイコールで比較(ローカル)
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE to_char(b.dt,'YYYYMMDD') = to_char(current_date,'YYYYMMDD');

DT       DT
-------- --------
13-05-01 13-05-01           -- current_dateは 13-05-01

SQL>
SQL> -- to_charして大なり小なり(リンク先)
SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE to_char(a.dt,'YYYYMMDD') > to_char(current_date,'YYYYMMDD');

DT       DT
-------- --------
13-04-02 13-04-02
13-04-30 13-04-30
13-05-01 13-05-01
13-05-02 13-05-02           -- current_dateは 13-04-01

SQL> SELECT * FROM test1@linkdb a JOIN test2 b ON a.dt=b.dt
  2   WHERE to_char(a.dt,'YYYYMMDD') < to_char(current_date,'YYYYMMDD');

DT       DT
-------- --------
13-03-31 13-03-31           -- current_dateは 13-04-01

SQL>

こんな感じです。

上記SQLを sysdate で試すと、すべて 13-05-01 つまりローカルDBサーバのシステム日付として評価されます。
なんでこういう挙動なのかわかってませんが、つまり、

  • sysdate : 必ずローカルDBサーバのシステム日付
  • current_date : リンク先かローカルかどっちで評価されるかわからない

ということでしょうか。
要するに法則性が見出せず、かなりカオス状態になります。実行計画に依存するのかもしれません。
まぁ、DBサーバの日付が食い違っているなんて事は運用上あってはならないことですが、トリビアの一つとして覚えておいていただければ話のネタになるかもですよ。

参考

ちなみに今回の検証を行った環境の構成。

  • リンク先DB Oracle 11g Enterprise
  • ローカルDB Oracle 11g Express

それぞれのリファレンス。