Home » SQL & PL/SQL » SQL & PL/SQL » 15 minutes gap in date (oracle 9i database)
15 minutes gap in date [message #291307] Thu, 03 January 2008 15:25 Go to next message
pink
Messages: 23
Registered: July 2003
Junior Member
I want to select records which have a 15 minutes gap in their activity date column which is of date type.. how do I compare without converting to char?
Re: 15 minutes gap in date [message #291308 is a reply to message #291307] Thu, 03 January 2008 15:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't really understand the question but 2 dates have 15 minutes gap if (date2-date1)*24*60 = 15.

Regards
Michel
Re: 15 minutes gap in date [message #291309 is a reply to message #291307] Thu, 03 January 2008 15:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I just wonder how would you achieve this WITH converting dates to char.

Simple subtraction of two dates gives their difference in days.
So ABS(DATE2 - DATE1) > 15/(24*60) says that the date difference is greater than 15 minutes (as day has 24*60 minutes).

Maybe you will need LAG analytic function to get the date from the previous row; not sure about exact requirements from your question.
Re: 15 minutes gap in date [message #291316 is a reply to message #291307] Thu, 03 January 2008 18:06 Go to previous messageGo to next message
pink
Messages: 23
Registered: July 2003
Junior Member
I want to check if a user logged in more than 5 times .. in the last 15 minutes. if yes I want to lock the person out..

When a user logs in .. I am storing that sysdate in a date formatted column .. so I need to compare dates in the same column not different columns..
Re: 15 minutes gap in date [message #291317 is a reply to message #291307] Thu, 03 January 2008 18:10 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>so I need to compare dates in the same column not different columns..
No, you do NOT need to compare dates; only count records that are newer than 15 minutes ago.
Re: 15 minutes gap in date [message #291377 is a reply to message #291316] Fri, 04 January 2008 01:03 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course for this you have to audit sessions.

Regards
Michel
Previous Topic: Changing tablespace (2 merged)
Next Topic: help me kick start with UTL_DBWS please
Goto Forum:
  


Current Time: Tue Dec 06 04:35:38 CST 2016

Total time taken to generate the page: 0.09496 seconds