CODEWITHSUNDEEP
mysqldatetime
Caiz22
Caiz22

Reputation: 817

Selecting rows that are within 2 hours from current time

I am using PHP with MySQL and would like to select rows that have a booking time within 2 hours from now. How do I compare what is in my database with the NOW() MySQL function?

I have columns pickupDate in the format yyyy-mm-dd and pickupTime in the format HH:mm (24-hour). I have tried creating a query with NOW() which returns the a 12-hour time as HH:mm:ss e.g. 2019-05-24 07:54:06 . I can't figure out how to format this to 19:54, or if I should use a different function instead.

For example, if the current date and time is 24/05/19 19:54:06, I would like to select rows between 19:54 and 21:54 on this date.

My table structure is:

referenceNo VARCHAR(100)    
pickupDate  DATE
pickupTime  VARCHAR(100)

Upvotes: 0

Views: 2759

Answers (2)

Rasa Mohamed
Rasa Mohamed

Reputation: 892

To add two hours in php

$hoursnow     = date('H:i');
$timestamp    = strtotime(date('H:i')) + 60*60*2;
$plusTwohours = date('H:i', $timestamp);

And $PlusTwohours using this variable frame the query like below

Sql Query:

$sqlQuery = 'select * from foodorder where pickupDate=DATE(NOW()) AND pickupTime>='.$hoursnow.' and pickupTime<='.$plusTwohours;
$result   = mysql_query($sqlQuery);

variable $result will have the values of query

For Second Scenario: Adding hours to end of the day May 24 23:30:00

This should be handle by two different date for same column pickupDate

$d = new DateTime('2011-01-01 23:30:30');

$startDate =  $d->format('Y-m-d H:i:s'); // For testing purpose assigned manually
$starttime = date('H:i');

// Here Process start, storing end date by adding two hours
$enddate1 = strtotime($startDate) + 60*60*2;

$enddate = date('Y-m-d', $enddate1); // Extracting date alone
$endtime = date('H:i', $enddate1); // Extracting time alone

Have to compare start and end date for column pickupDate, here is the query

$sqlQuery = "select * from foodorder where pickupDate>=DATE(".$startDate.") AND pickupDate<=DATE(".$enddate.") AND pickupTime>='".$starttime."' AND pickupTime<='".$endtime."'";
$result   = mysql_query($sqlQuery);

Upvotes: 0

Nick
Nick

Reputation: 147166

You need to create a DATETIME compatible value out of your pickupDate and pickupTime (which you can do by CONCATing them together), then you can compare that with a time range from NOW() to 2 hours later:

SELECT *
FROM yourtable
WHERE CONCAT(pickupDate, ' ', pickupTime) BETWEEN NOW() AND NOW() + INTERVAL 2 HOUR

Demo on dbfiddle

Upvotes: 2

Related Questions