Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7400
  • Last Modified:

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

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
jtcy
Asked:
jtcy
  • 4
  • 4
  • 2
  • +1
1 Solution
 
steelseth12Commented:
$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
 
jtcyAuthor Commented:
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
 
dansotoCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
jtcyAuthor Commented:
still...nothing returned
0
 
steelseth12Commented:
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
 
dansotoCommented:
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
 
jtcyAuthor Commented:
I run

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

and it gave no result
0
 
dansotoCommented:
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
 
jtcyAuthor Commented:
Thanks! Now that works perfect!
0
 
dansotoCommented:
Cool.  Sorry for the earlier confusion.  I haven't had my coffee yet ...
0
 
zodozCommented:
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
 
zodozCommented:
sorry, forget the extra colon at the end.  typo...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now