Q:

Oracle TIMESTAMP Comparison with TO_TIMESTAMP function

I am having a problem getting the result I expect from an Oracle query.
The TO_TIMESTAMP I am using appears to work fine:
SELECT TO_TIMESTAMP('11-16-2014 00:00:00', 'mm-dd-yyyy hh24:mi:ss') FROM DUAL

Returns
    2014-11-16 00:00:00
My table AUDIT has a column CURRENT_TIMESTAMP of Oracle type TIMESTAMP(6).  I don't know if it helps but this column also has these attributes:

DATATYPE=93
COLUMN_SIZE=11
DECIMAL_DIGITS=6
NUM_PREC_RADIX=10
CHAR_OCTET_LENGTH=11

Lets look at the table size:
SELECT count(*) FROM RPT.AUDIT
returns
623981
This table grows about 500 rows a day.  So I would expect this query to return a number under 1000.
Instead I get the whole table:
SELECT count(*) FROM RPT.AUDIT WHERE CURRENT_TIMESTAMP > TO_TIMESTAMP('11-16-2014 00:00:00', 'mm-dd-yyyy hh24:mi:ss')
returns
623981
Thanks if you can help.

A:

Because CURRENT_TIMESTAMP is the name of an Oracle function the database prefers to use the function instead of your column - and thus, since CURRENT_TIMESTAMP (the function) is always greater than a time in the past the predicate returns TRUE for every row in the table, and thus every row gets counted. If you put a table alias in your query and qualify the column name with the alias you should get what you expected:
SELECT count(*)
  FROM RPT.AUDIT a
  WHERE a.CURRENT_TIMESTAMP > TO_TIMESTAMP('11-16-2014 00:00:00', 'mm-dd-yyyy hh24:mi:ss')

Or you can just refer to the column as RPT.AUDIT.CURRENT_TIMESTAMP if you like.
Share and enjoy.

