0

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.

Improve this question
2
  • Do you have a table in your database with timestamps? Commented Nov 26, 2013 at 12:47
  • yes, i have table with timestamps. Commented Nov 26, 2013 at 12:48

4 Answers 4

Reset to default
2

In MySQL,

SELECT fields
FROM table
WHERE timefield BETWEEN DATE_SUB('$date', INTERVAL $tohour HOUR) AND '$date'
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

what is your $todate value?.did you mean $tohour?
Er, SELECT 1 BETWEEN 3 AND 0; -> 0
ya true..this query works when i interchange it so please update it.
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)
Improve this answer

3 Comments

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 .
in database, data is between 2012-08-29 21:18:23 to 2012-08-29 21:49:23.
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?
0

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

Improve this answer

Comments

0

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:

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.