Solved

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

Posted on 2007-11-16
14
5,417 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

696 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