9

I have a problem building a query in oracle. I have table with column "DATE_CREATE", which has a type of "TIMESTAMP". Example of one value is:

2012-10-20 05:43:47:001000

I would like to build a where clause for selecting rows with create column newer than 15 minutes ago. For now I have a query like this (which return no rows, but it should):

SELECT DATE_CREATE,ID
FROM TABLE
WHERE DATE_CREATE >= CURRENT_TIMESTAMP - interval '15' minute

Help please...

marc_s
  • 1,007
  • 3
  • 14
  • 24
  • 1
    what is the error thrown? – CodeBird Apr 15 '14 at 09:17
  • Do you really have a column called `CREATE`? That is a reserved word, you need to quote it in order to use it `"CREATE"`. In general you should avoid using reserved words as object names - precisely for that reason –  Apr 15 '14 at 09:19
  • No, I have just put an example, it's called "DATE_CREATE". And this query doesn't throw an error (I'm sorry, I mixed up), this one shows no results - there actually are - more than 100... I get an error if I try with custom date, let's say: "WHERE DATE_CREATE >= '2012-10-20 05:30:00:000000' - then I get this: ORA-01861: literal does not match format string – marc_s Apr 15 '14 at 09:22

1 Answers1

20

Try this:

SELECT DATE_CREATE,ID
FROM TABLE
WHERE DATE_CREATE >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(15, 'MINUTE')
StephaneM
  • 4,779
  • 1
  • 16
  • 33
  • 1
    This one works! Thank you. But I'm wondering why mine returns zero rows? Where is the difference? – marc_s Apr 15 '14 at 09:40
  • I prefer this version when I'm unable to hard-code the minutes. Passing a quoted number like '15' is painful. – rshdev Jan 16 '19 at 20:01