CODEWITHSUNDEEP
oracle-databasetime
AAA
AAA

Reputation: 2450

Oracle Timestamp - find records older than 60 seconds

In an Oracle table, I have an updated_dt stored like this: 25-NOV-14 10.20.32.000000 PM

I would like to select from this table where the records are more than 60 seconds old. How can I do this?

I tried:

select systimestamp-60/(24*60*60) time from dual

to find the time from 60 seconds ago but if I put in my where clause:

where updated_dt < (select systimestamp-60/(24*60*60) time from dual)

then nothing ever comes up...

Any help would be appreciated!

Upvotes: 0

Views: 4329

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59533

Assuming the column is a DATE or TIMESTAMP [WITH [LOCAL] TIMESTAMP] column and not a VARCHAR2 you can use this one:

where updated_dt < systimestamp - INTERVAL '60' SECOND

I always wonder why people use gain and again this stupid SELECT ... from dual syntax

Upvotes: 3

psaraj12
psaraj12

Reputation: 5072

Kindly Use the below

where to_date(to_char(updated_dt,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') > to_date(to_char(systimestamp,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss')- (60/(24*60*60))

Upvotes: 0

Related Questions