CODEWITHSUNDEEP
oracle-database
user1471980
user1471980

Reputation: 10626

how do you convert to_char to datetime format in Oracle

I am trying to retrieve data for last 5 minutes from an oracle table:

SELECT to_char(a.collection_timestamp, 'dd-mm-yyyy HH:MM:SS') collection_timestamp
  FROM table_name
 WHERE collection_timestamp > SYSDATE - 5 / 1440

I get results older than 5 minutes ago. Do I have to convert to COLLECTION_TIMESTAMP into oracle datetime? How?

select sysdate from dual

17-FEB-15

Desc <table name>
COLLECTION_TIMESTAMP  DATE

This is what I get when I execute that query:

17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:33
17-02-2015 12:02:29
17-02-2015 12:02:24
17-02-2015 12:02:24
17-02-2015 12:02:24
17-02-2015 12:02:24
17-02-2015 12:02:24
17-02-2015 12:02:24
17-02-2015 12:02:41
17-02-2015 12:02:07
17-02-2015 12:02:29
17-02-2015 12:02:17
17-02-2015 12:02:17
17-02-2015 12:02:17
17-02-2015 12:02:17

current time is 12:43

Upvotes: 0

Views: 428

Answers (1)

Boneist
Boneist

Reputation: 23578

You're using a format of: 'dd-mm-yyyy HH:MM:SS'

Change that to: 'dd-mm-yyyy HH:MI:SS'

Your where clause will be restricting the data to the last five minutes, but you're displaying the minutes part of your date-time as the month number.

Upvotes: 4

Related Questions