CODEWITHSUNDEEP
sqloracle-database
Evil Washing Machine
Evil Washing Machine

Reputation: 1341

Oracle SQL to select rows with timestamp not more than 1 week ago?

I want to select all rows which have a timestamp of less than a week ago in a column called UPDATE_TIME in a table called generic. From an earlier question I developed this script:

select * from generic where 'UPDATE_TIME' < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK))

But this is giving me this error:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 1 Column: 92

What am I doing wrong?

Upvotes: 0

Views: 1775

Answers (1)

user330315
user330315

Reputation: 

select *
from generic
where timestamp '1970-01-01 00:00:00' + numtodsinterval(update_time,'SECOND') > current_timestamp - interval '7' day

  • There is no unix_timestamp() in Oracle. This needs to be done using:

    timestamp '1970-01-01 00:00:00' + <number of seconds in epoch>

    to add the number of seconds they must be converted to an interval type. That's what 

    numtodsinterval(update_time,'SECOND')

    is doing

  • there is no date_sub in Oracle
  • there is no now() in Oracle
  • 'UPDATE_TIME' is a string literal not a column name

If however update_time is a date or timestamp column, then the whole conversion is not necessary:

select *
from generic
where update_time > current_timestamp - interval '7' day

Upvotes: 2

Related Questions