Dates without time

Oracle’s DATE data type stores both date and time components. If a date is stored without specifying a time component then the time is implicitly set to 0 hours, 0 minutes and 0 seconds (00:00:00), which is exactly midnight at the start of the date.

Nothing unusual with this but quite regularly a data model will call for dates without any time component. The problem resulting from this scenario is how to ensure that time components other than 00:00:00 don’t manage to find their way into the data. If dates with time manage to find their way into such data then various problems occur. For example, checks for equality will fail, e.g.:

SELECT ...
FROM   ...
WHERE  date_col = TRUNC(SYSDATE)

Range searches will fail to find data on the last date of the range, e.g.:

SELECT ...
FROM   ...
WHERE  date_col BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)

GROUP BY expressions will report groups down to the specific time, e.g.:

SELECT ...
FROM   ...
GROUP  BY
      date_col

The solution to the problems noted about could be to TRUNC the date whenever the date column is referenced, e.g.:

SELECT ...
FROM   ...
GROUP  BY
       TRUNC(date_col)

and this is what regularly occurs in applications I have had to deal with. The line of thinking here is clearly “Since I’m not sure if a date with a time component has managed to get into the system, I’ll ensure I only use the date part of the DATE column”. Unfortunately all those TRUNC functions are overhead and can easily to lead of suboptimal query execution plans.

Other systems have taken the approach of trying to prevent time components being stored by removing the time components from the dates when storing the data, e.g.:

INSERT INTO ...
VALUES (...
       ,TRUNC(some_date_parameter)
       ...)

Clearly this is the better approach than storing the time component and trying to deal with dates with time. However the problem still remains that a rogue entry can make its way into the data. Perhaps an infrequently used update routine is missing the TRUNC or perhaps some data patch simply used SYSDATE instead of TRUNC(SYSDATE). At the end of the day you’re still not 100% certain that all the entries are “timeless”. Once doubt sets in then you’ll find TRUNCs cropping up into the SELECTs and it’s all downhill from there…

The proper solution to this problem is, like all good solutions should be, quite simple. Since Oracle does not provide a date only data type create one! This is done by adding a CHECK constraint:

ALTER TABLE ...
ADD CONSTRAINT date_col_chk
CHECK ( date_col = TRUNC(date_col) )

With the above constraint in force you can rest in the comfort of knowing that all the dates in the column are guaranteed have a time component of 00:00:00. No need for any messy TRUNC functions littered throughout the code. DML operations trying to sneak time components onto the dates will be rejected with an ORA-02290 exception.

Advertisements