CODEWITHSUNDEEP
mysqlsqloracle-database
Henrique Kieckbusch
Henrique Kieckbusch

Reputation: 190

An Oracle Select with time

I'm converting a mysql system to use oracle, but I'm having problems with this query:

SELECT * 
  FROM recent_activity 
 WHERE TIME_TO_SEC(TIMEDIFF(NOW(), lastactivity )) < 5

I know that NOW() uses systimestamp and lastactivity is a timestamp. I'm trying to check activities in the last 5 seconds.

Do you know how I can write this in Oracle?

Upvotes: 0

Views: 171

Answers (3)

Shaun Peterson
Shaun Peterson

Reputation: 1790

create table blah (blah1 timestamp, blah2 timestamp);

insert into blah values (systimestamp, systimestamp - .00005);

select * from blah;

Blah1                                   Blah2
01/OCT/13 02:17:41.300000000 PM         01/OCT/13 02:17:37.000000000 PM

so in your case you should be simply able to do 

select * 
from recent_activity
where lastactivity > (NOW() - .00005)

This actually gives you the last 4.3 seconds as it rounds but probably close enough for most solutions or make it .00006 to get 5.some seconds if it is better to go over.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36798

select * from recent_activity where systimestamp-lastactivity < interval '5' second;

Upvotes: 1

Henrique Kieckbusch
Henrique Kieckbusch

Reputation: 190

Ok, now I think I got this working:

 SELECT * FROM recent_activity WHERE EXTRACT (DAY FROM (systimestamp-lastactivity))*24*60*60+EXTRACT (HOUR FROM (systimestamp-lastactivity))*60*60+EXTRACT (MINUTE FROM (systimestamp-lastactivity))*60+ EXTRACT (SECOND FROM (systimestamp-lastactivity)) < 5

Upvotes: 0

Related Questions