Solved

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

Posted on 2007-11-16
14
5,259 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Optimize the query 5 43
php56-php-mcrypt for rhel7 php56 1 54
MVC - procedural PHP 10 38
ability to create nested list on the UI using PHP, HTML, Javascript 7 45
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

837 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