Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL: How to select rows with datetime less than 24 hours from today?

Posted on 2007-11-16
14
Medium Priority
?
5,659 Views
Last Modified: 2013-12-13
I wanna do:

$today = date("Y-m-d H:i:s");

$result = mysql_query("SELECT sent_date FROM invitations WHERE email='$email' AND uid='$session_uid' AND '$today' - `sent_date` < 24 hours from now");

how can I compare the sent_date, which is a datetime format with today which is also a datetime, and their difference is 24 hours.
0
Comment
Question by:jtcy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
14 Comments
 
LVL 20

Expert Comment

by:steelseth12
ID: 20297793
$result = mysql_query("SELECT sent_date FROM invitations WHERE email='$email' AND uid='$session_uid' AND `sent_date` < SUBDATE( CURRENT_DATE, INTERVAL 1 DAY)");
0
 

Author Comment

by:jtcy
ID: 20297888
donno why it's returning no rows....

one of the row has sent_date as 2007-11-16 07:07:32, should be able to be found...
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20297893
Actually, using the above statement you will get all records with a timestamp of yesterday which could be older than 24 hours.  To get to the exact hour you can use the same statement except the use:
<= SUBDATE( CURRENT_DATE, INTERVAL 24 HOUR)");

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jtcy
ID: 20297974
still...nothing returned
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 20297990
try

$result = mysql_query("SELECT sent_date FROM invitations WHERE email='$email' AND uid='$session_uid' AND `sent_date` < SUBDATE( NOW(), INTERVAL 1 DAY)");
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20298005
run your query without any restrictions except the date restriction and see if it returns any.
Also run the query at a command line or in a query browser substituting values you know to be true and see if it works...
$result = mysql_query("SELECT sent_date FROM invitations WHERE `sent_date` <= SUBDATE( CURRENT_DATE, INTERVAL 24 HOUR)");

Open in new window

0
 

Author Comment

by:jtcy
ID: 20298015
I run

SELECT sent_date FROM invitations WHERE `sent_date` <= SUBDATE( CURRENT_DATE, INTERVAL 24 HOUR)

and it gave no result
0
 
LVL 7

Accepted Solution

by:
dansoto earned 2000 total points
ID: 20298127
Sorry... we are working backwards here and not precise enough... this should work and I tested on my own DBs. ..need to use >...not less than.  The reason is the time we are comparing against is a point in time exactly 24 hours from now until the present.  So we are trying find something greater than that point in time.

I added timestamps so we can be more precise on the 24 hour rule.

SELECT timestamp(sent_date) FROM invitations WHERE `sent_date` >= SUBDATE( timestamp(now()), INTERVAL 24 HOUR)

Open in new window

0
 

Author Comment

by:jtcy
ID: 20298185
Thanks! Now that works perfect!
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20298195
Cool.  Sorry for the earlier confusion.  I haven't had my coffee yet ...
0
 

Expert Comment

by:zodoz
ID: 20309061
interesting, assuming sent_date is a DATETIME object, you can also do it like the following, may be easier to read.
SELECT sent_date FROM invitations WHERE sent_date >= SUBDATE(NOW(), "24:00:00:")

Open in new window

0
 

Expert Comment

by:zodoz
ID: 20309065
sorry, forget the extra colon at the end.  typo...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

618 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