get data between hours in php

Question

I have a time like this 2013-08-12 12:58:14. Now I want hour so I use following code:

$date = "2013-08-12 12:58:14";
$date = strtotime($date);
$hour = date('H', $date);

So I get $hour=12; now I have a other parameter $tohour=5 (it is user defined which value is between 1 to 24); now 12-5=7 means fetch data between time 7 to 12.

Another example:

Suppose time is 2013-08-12 01:15:23 and $tohour=10.
Now this time the data fetch between 2013-08-11 15:15:23 to 2013-08-12 01:15:23.

So how to achieve it? I try to explain my question, but if it is not clear, please let me know or fill free to edit my question and title too.

Do you have a table in your database with timestamps? – sushant-hiray Nov 26 '13 at 12:47 — sushant-hiray, Nov 26 '13 at 12:47
yes, i have table with timestamps. – DS9 Nov 26 '13 at 12:48 — DS9, Nov 26 '13 at 12:48

Ramesh · Accepted Answer · 2013-11-26T14:50:19.613

2

In MySQL,

SELECT fields
FROM table
WHERE timefield BETWEEN DATE_SUB('$date', INTERVAL $tohour HOUR) AND '$date'

edited Nov 26 '13 at 14:50

answered Nov 26 '13 at 12:50

Ramesh

4,223
2
16
24

what is your `$todate` value?.did you mean `$tohour`? – DS9 Nov 26 '13 at 13:14
1

Er, SELECT 1 BETWEEN 3 AND 0; -> 0 – Strawberry Nov 26 '13 at 14:08
ya true..this query works when i interchange it so please update it. – DS9 Nov 26 '13 at 14:11

score 1 · Answer 2 · answered Nov 26 '13 at 12:50

1

You can easily get the from and to times using strtotime:

$baseTime = strtotime('2013-08-12 01:15:23');
$from = strtotime('+'.$hour.' hours',$basetime);
$to = strtotime('+'.$tohour.' hours',$basetime);
echo "From: ".date("Y-m-d H:i:s",$from)."\n";
echo "To: ".date("Y-m-d H:i:s",$to)."\n";

Or in mysql:

SELECT
   *
FROM
   `table`
WHERE
    `time` 
BETWEEN
    DATE_ADD('$time',INTERVAL $hour HOUR)
AND
    DATE_ADD('$time',INTERVAL $tohour HOUR)

answered Nov 26 '13 at 12:50

Jesper Blaase

2,320
16
13

i try your query,it's look like `SELECT * FROM cdr where calldate BETWEEN DATE_ADD('2012-08-29 23:00:00',INTERVAL 23 HOUR) AND DATE_ADD('2012-08-29 23:00:00',INTERVAL 5 HOUR)` but it didnot fetch data . – DS9 Nov 26 '13 at 13:26
in database, data is between `2012-08-29 21:18:23` to `2012-08-29 21:49:23`. – DS9 Nov 26 '13 at 13:28
That basically translates to this query: SELECT * FROM cdr WHER calldate BETWEEN '2012-08-30 22:00:00' AND '2012-08-30 04:00:00' Is that what you want? – Jesper Blaase Nov 26 '13 at 13:29

score 0 · Answer 3 · answered Nov 26 '13 at 12:56

In plain PHP:

$toHour = 10;
$dateTo = \DateTime::createFromFormat('Y-m-d H:i:s', '2013-08-12 12:58:14');
$dateFrom = clone $dateTo;
$dateFrom->sub(new \DateInterval('PT'.$toHour.'H'));
var_dump(
    'From: ' . $dateFrom->format('Y-m-d H:i:s'),
    'To: ' . $dateTo->format('Y-m-d H:i:s')
);

But personally I would go with MySQL solution.

PS. Ofcourse this solution is for PHP 5 >= 5.3.0

score 0 · Answer 4 · edited May 23 '17 at 10:25

I prefered to do some compute in PHP and use simple mysql query.

$timeEnd = '2013-08-12 01:15:23';
$toHour = 10;

$timeStart = date('Y-m-d H:i:s', strtotime($timeEnd) - $toHour * 3600);

echo "Fetch data between $timeStart to $timeEnd";
// Result: Fetch data between 2013-08-11 15:15:23 to 2013-08-12 01:15:23

Then, we got 2 date string, do db query with them, see:

get data between hours in php

4 Answers4

Linked