ORACLEのカラムの型のひとつにDATE型があります。例えば、受付日やデータ登録日といったような日付のデータを登録する時に使用します。
このDATE型、実は時分秒まで扱うことができます。そのため、想定外の動作の原因となることがあります。
DATE型とは
ORACLEのDATE型は、日付および時刻を格納する型です。具体的には、年月日時分秒を格納できます。7バイトの領域を使用します。
注意点ですが、名前から日付のみを格納する型だと思われがちですが、時刻まで格納することができます。
そのため、想定外の動作の原因となることがあります。
想定外である時刻の格納
問題となるのは、日付のみを格納する想定にもかかわらず、時刻が紛れ込んでしまった時に起こります。
例えば、受付日という日付のみを格納する想定の項目で考えてみます。そこに、時刻が入ったデータが紛れ込んでしまうことで様々な問題が起ります。
-- テストテーブル A RECEPTIONDATE --- ------------------- 001 2017/02/03 00:00:00 002 2017/02/04 00:00:00 003 2017/02/04 19:20:31 ← 想定外のデータ 004 2017/02/05 00:00:00
等価条件の検索の対象とならない
受付日がある日のデータをとりだそうとした時に、検索の対象とならなくなります。
-- 受付日が2/4のデータを取得したい
SELECT *
FROM test_table
WHERE receptiondate = TO_DATE('20170204','YYYYMMDD')
;
A RECEPTIONDATE
--- -------------------
002 2017/02/04 00:00:00
範囲条件の検索がうまくいかない
受付日がある範囲のデータをとりだそうとした時に、うまくいかないことがあります。
-- 受付日が2/4より大きいデータを取得したい
SELECT *
FROM test_table
WHERE receptiondate > TO_DATE('20170204','YYYYMMDD')
;
A RECEPTIONDATE
--- -------------------
003 2017/02/04 19:20:31
004 2017/02/05 00:00:00
集計がうまくいかない
SQL*Plusなどで受付日ごとの件数を集計しようとした時に、うまくいかないことがあります。
--受付日ごとの件数を取得したい
--※SQL*Plusの日付型の表示がYY-MM-DDになっている
SELECT receptiondate
,COUNT(*) cnt
FROM test_table
GROUP BY receptiondate
;
RECEPTIONDATE CNT
------------- -----
17-02-03 1
17-02-04 1
17-02-04 1
17-02-05 1
対策
対策としては、以下になります。それぞれに欠点があるため、気をつける必要があります。
データの登録・更新処理を徹底チェック
アプリケーションのデータの登録・更新処理を徹底的にチェックし、時刻が登録されるのを防ぎます。
欠点は、SQL*Plusなどで行われるアプリケーション以外からのテーブル更新が考慮しきれない点です。
文字列型に変更
CHARやVARCHARといった文字列型の8桁に変更することで、時刻が登録されるのを防ぎます。
欠点は、日付型の計算や関数を使用するためには、一度DATE型に変換する必要があるという点です。
--3日前を計算し表示 SELECT TO_DATE(receptiondate,'YYYYMMDD') -3 FROM test_table ; --月末日し表示 SELECT LAST_DAY(TO_DATE(receptiondate,'YYYYMMDD')) FROM test_table ;
時刻が入っているデータの抽出や補正
日付のみを格納する想定にもかかわらず、時刻が紛れ込んでしまった時に、対象の抽出方法や補正方法は以下になります。
抽出
切捨てを行うTRUNC関数を利用します。元の値と時刻を切捨てた値を比較して、差異があるデータを抽出します。
SELECT * FROM test_table WHERE receptiondate <> TRUNC(receptiondate) ;
補正
時刻を切り捨てて補正すること考えます。
こちらも切捨てを行うTRUNC関数を利用します。切捨てた値で更新を行います。
UPDATE test_table SET receptiondate = TRUNC(receptiondate) WHERE receptiondate <> TRUNC(receptiondate) ;
まとめ
DATE型に時刻を格納することができること、日付のみを格納する想定している場合に時刻が紛れん込んでしまうと問題となること、理解しておきましょう。
