0

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)
Caiz22
  • 697
  • 3
  • 12
  • 21

2 Answers2

2

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

Nick
  • 138,499
  • 22
  • 57
  • 95
  • For some odd reason this isn't returning any rows, even when I replace pickupDate and pickupTime with values that are definitely in my table – Caiz22 May 24 '19 at 07:33
  • @Caiz22 that is odd. Are you sure those values are within two hours of what the server thinks is `NOW()`? – Nick May 24 '19 at 07:38
  • @Caiz22 can you perhaps share your table structure and some sample data? – Nick May 24 '19 at 07:52
  • ```NOW()``` returns 2019-05-24 07:54:06, I suspect it may be comparing against AM time – Caiz22 May 24 '19 at 07:59
  • @Caiz22 `NOW()` returns 24 hour time. So that is 7:54 AM – Nick May 24 '19 at 08:00
  • @Caiz22 in your original question you say the columns are called `pickupDate` and `pickupTime`. I'm confused as to what `bookingDate` and `bookingTime` are. Or did you want to find booking times within 2 hours of a pickup time? – Nick May 24 '19 at 08:21
  • In response to your other reply, it was 7:54 PM in my current location. Editing my question to have bookingDate and bookingTime was my mistake (I meant pickup). Yes that's correct, I want to find booking times within 2 hours of the current time – Caiz22 May 24 '19 at 09:05
  • So do you mean something like `CONCAT(pickupDate, ' ', pickupTime) BETWEEN bookingTime AND bookingTime + INTERVAL 2 HOUR` where `bookingTime` is a datetime string e.g. `2019-05-24 11:20:00`? – Nick May 24 '19 at 09:07
  • *"I want to find booking times within 2 hours of the current time "* @Caiz22 or use [SYSDATE()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sysdate) then you don't have to deal with timezone settings with [NOW()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now) to have to deal with that. – Raymond Nijland May 24 '19 at 14:27
0

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);
Rasa Mohamed
  • 882
  • 1
  • 6
  • 14
  • Thanks this worked, you're original answer was most similar to what I used to get it working - only removed the date call `$timestamp = strtotime($time) + 60*60*2; $plusTwohours = date('H:i', $timestamp);` – Caiz22 May 24 '19 at 11:40
  • The only thing missing is when it goes onto a new day e.g. current time is 24 May 23:30, 2 hours from then will be 25 May 01:30. I am unsure how to account for this – Caiz22 May 24 '19 at 12:03
  • @Caiz22 From my understand this is right! May I know how do you expect for this scenario. If we add only hours to hours without date then result will be `24 May 23:30 to 24 May 01:30` so this is wrong way to get data from DB! – Rasa Mohamed May 24 '19 at 12:22
  • @Caiz22 `Thanks this worked` : welcome, You can show your Thanks by voting up or Mark this as answer :-) – Rasa Mohamed May 24 '19 at 12:23
  • @Caiz22 Answer update for `e.g. current time is 24 May 23:30, 2 hours from then will be 25 May 01:30` – Rasa Mohamed May 24 '19 at 14:14