CODEWITHSUNDEEP
sqloracle-databasedatetimetimestamp
Marcus
Marcus

Reputation: 3869

Search data using Timestamp datatype in Oracle Where clause

I have table Alert_Logs in which there are 2 columns KPI_DEF_ID and TIMESTAMP. The TIMESTAMP column is of timestamp datatype. I want to search a data using timestamp column in oracle sql where clause. I tried below query but it returns nothing.

Select * from ALERT_LOGS WHERE KPI_DEF_ID = 1000571 and TIMESTAMP = to_timestamp('17.10.15 00:02:58','DD.MM.YY HH24:MI:SS');

Upvotes: 1

Views: 11724

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

and TIMESTAMP = to_timestamp('17.10.15 00:02:58','DD.MM.YY HH24:MI:SS')

Since your column data type is timestamp, it would have more elements than datetime.

Also, the equality condition does not match any rows. You need to use a range condition.

For example, to get all the rows having timestamp between 2 AM and 3 AM:

WHERE TIMESTAMP >= to_timestamp('17.10.15 00:02:00','DD.MM.YY HH24:MI:SS')
AND   TIMESTAMP <  to_timestamp('17.10.15 00:03:00','DD.MM.YY HH24:MI:SS')

The good part using a range condition is that it will be efficient in terms of performance. Using TO_CHAR would suppress any regular index on the timestamp column. The range condition would do an index range scan.

Upvotes: 2

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

You are trying to compare timestamp with date. Timestamp also have milliseconds. Assuming TIMESTAMP is of type timestamp likely this will work

Select * from ALERT_LOGS WHERE KPI_DEF_ID = 1000571 and to_char(TIMESTAMP, 'DD.MM.YY HH24:MI:SS') = '17.10.15 00:02:58';

This is how timestamp is represented.

SQL> select to_timestamp('02.12.15 08:40:54', 'DD.MM.YY HH24:MI:SS') from dual;

TO_TIMESTAMP('02.12.1508:40:54','DD.MM.YYHH24:MI:SS')

02-DEC-15 08.40.54.000000000 AM

Upvotes: 1

Related Questions