CODEWITHSUNDEEP
oracle-database
gbshuler
gbshuler

Reputation: 182

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:

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.

Upvotes: 1

Views: 955

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 3

Related Questions