1

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');
Improve this question
1
  • That means there are no rows with that timestamp and kpi_def_id. Why do you think something it is wrong? Commented Dec 2, 2015 at 8:40

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

can you please tell me lalit how to use range condition ? And you are correct lalit equality condition is not matchin rows.
@Rahul See the example in the answer.
@Rahul You mean you do not want rows matching with that exact timestamp? And return all other rows?
@Rahul Yes, just use <> OR !=
1

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

Improve this answer

3 Comments

@DurgaViswanathGadiraju No. It is not efficient solution. I wouldn't do that as it would degrade the performance. Since, using TO_CHAR would suppress any regular index. The best approach is to use range condition as I have mentioned in my answer.
@Lalit Good point. But if there is index on KPI_DEF_ID, it might not be big problem. In some cases there might not be indexes on timestamp columns.
@DurgaViswanathGadiraju Most reporting systems have indexes on the timestamp column, the whole point in having a timestamp column is to filter rows based on the timeline.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.