Solved

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

Posted on 2007-11-16
14
5,026 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
  • 4
  • 4
  • 2
  • +1
14 Comments
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
$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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:jtcy
Comment Utility
still...nothing returned
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jtcy
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks! Now that works perfect!
0
 
LVL 7

Expert Comment

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

Expert Comment

by:zodoz
Comment Utility
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
Comment Utility
sorry, forget the extra colon at the end.  typo...
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now