2012-04-18Date Types

Most obfuscations are base upon DATE types. The Oracle database is particularly vulnerable in this respect because it has only one DATE type, which always includes the time as well.

It has become common practice to use the TRUNC function to remove the time component. In fact, it does not remove the time—the Oracle Database has no DATE type without time component—it just sets it to midnight. Using the TRUNC function on both sides of a comparison, however, allows to search for a date—e.g, yesterday:

SELECT ...
  FROM ...
 WHERE TRUNC(date_column) = TRUNC(sysdate - INVERVAL '1' DAY)

It is a perfectly valid and correct statement, but cannot use an index on DATE_COLUMN properly. It is like explained in Section 2.1; TRUNC(date_column) is something entirely different than DATE_COLUMN for the database. Remember the black box.

There is a rather simple solution for that problem: a function based index.

CREATE INDEX index_name
          ON table_name (TRUNC(date_column))

But then, you must always use this column with TRUNC in the where clause. It it is used inconsistently—sometimes with, sometimes without TRUNC—you would need two indexes!

The problem also appears with databases that have a pure date type. When searching for a longer period—like in the following MySQL query:

SELECT ...
  FROM ...
 WHERE DATE_FORMAT(date_column, "%Y-%M")
     = DATE_FORMAT(now()      , "%Y-%M')

The condition formats both dates as String, which contains only year and month. Again, an absolutely correct query, which has the same problem like the TRUNC query. However, the solution from above does not work here—MySQL has no have function based indexes.

But there is a generic solution that works with all databases. Write the condition as explicit range condition.

SELECT ...
  FROM ...
 WHERE date_column BETWEEN 
                   quarter_begin(?) 
               AND quarter_end(?)

If you have done your homework, you probably recognize that pattern from the exercise about all employees aged 42.

With this method, a straight index on DATE_COLUMN is enough. The functions QUARTER_BEGIN and QUARTER_END compute the boundary dates. This calculation can become a little complex, because the between operator always includes the boundary values. The QUARTER_END function must return a time just before the first day of the next quarter, if the DATE_COLUMN has a time component. This logic can be well hidden in the functions.

The following samples show QUARTER_BEGIN and QUARTER_END for some databases.

You can use similar auxiliary functions for other periods—most of them will be less complex than the examples above. Especially when using greater-equal (>=) and smaller-than (<) conditions instead of the between operator.

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

Another common obfuscation is to compare dates as strings—like in the following PostgreSQL example:

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

The problem is, again, the conversion of the DATE_COLUMN. These conversions are very often done if a developer does not know how to pass a date to the database. But the answer is very simple. The same way you should pass all values to the database: with bind parameters. That means, you can pass an java.util.Date object, for example, as bind parameter to the database. Yet another benefit of bind parameters.

If you cannot provide the value as date type, you should convert the search term instead of the table column:

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

That allows a straight index on DATE_COLUMN. Moreover, it converts the input string only once. The other statement needs to convert all the dates from the table before they can be compared against the string literal.

Whatever change you do—using a bind parameter or converting the other side of the comparisons—you can easily introduce a bug. If the DATE_COLUMN includes a time component—like the Oracle DATE type does—you must use an explicit range condition:

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

Always consider an explicit range condition when comparing dates.

date_column LIKE SYSDATE

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