?
Solved

Select statement to get all records older than 24 hours

Posted on 2006-05-12
6
Medium Priority
?
1,833 Views
Last Modified: 2008-01-09
I am trying to create a select statement that show all the records that are older than 24 hours. I do not want to show all records that are a day old. I am looking for comparing if it has been longer than x number of hours. The sql needs to be flexible enough so I can plug in an arbitrary number of hours (like 6, 12 or 24) and then get all of those records back.
0
Comment
Question by:omicronpersei8
  • 4
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Yuval_Shohat
ID: 16672842
check this link:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
i think the first query shown there is something you will find very usafull.
(dont forget to relace the Currentdate() with a data/time/datetime column in your table).

In addition to that, there are many other date/time functions that could be very helpful.

-=Yuval=-
0
 

Author Comment

by:omicronpersei8
ID: 16672862
I've been reading that page for the last two hours and still do not see the answer. Hence the question here to the experts who do know how to interprite that information. ;)
0
 
LVL 8

Expert Comment

by:Yuval_Shohat
ID: 16673072
ok, lets assum you have a table called DataTable which contains a column called SomeData and a column named InsTime (which holds time values of the time when the record was inserted in to the database).

the query should be something like:
SELECT SomeData FROM DataTable WHERE InsTime >= DATE_SUB(CurrentTime(),INTERVAL 6 HOUR)

this query will result in all the rows from DataTable which have InsTime larger or equaly to the currenttime minus 6 hours.
since you have mentiond that you want the query to be flaxible, and able to choose 6, 12, 24 hours... all there is to do is to recive the time length from some varible (for example, html form with a dropdown box with the options of 6, 12 and 24....) and set that variable in to the query.
that is, lets assume you are using html and php and the html form is sent via post method and the dropdwon box name is DorpDown then the query will look like this:

$Query="SELECT SomeData FROM DataTable WHERE InsTime >= DATE_SUB(CurrentTime(),INTERVAL".$_POST['DropDown']."  HOUR)"...

hope this makes it clearer.
-=Yuval=-
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Accepted Solution

by:
Yuval_Shohat earned 2000 total points
ID: 16673083
ooopps, from some resone i understod you wanted the results that are less than X hours old, but reading the title again, i see you need it the other way around.... so here is a fix:

SELECT SomeData FROM DataTable WHERE InsTime <= DATE_SUB(CurrentTime(),INTERVAL 6 HOUR)

the  >= needs to be <=

-=Yuval=-

0
 

Author Comment

by:omicronpersei8
ID: 16673121
Ah I see. Thank you very much. I had an error that sql did not have CurrentTime() function so I put now() in its place and it worked.

SELECT * FROM email_holder WHERE creation_date <= DATE_SUB(Now(),INTERVAL 6 HOUR)

0
 
LVL 8

Expert Comment

by:Yuval_Shohat
ID: 16673129
:)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question