How do I check hours between two date/times ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: How do I check hours between two date/times ?

Post by McInfo »

1. You can calculate the time difference in SQL with TIMESTAMPDIFF().

2. Regarding GROUP BY, imagine how you would do it manually. Given a list of items to be grouped by K, you would take the each item in order and see if its K matches one you have already found. If K is new, you keep the item. If K is known, you discard the item. So it's the first item found that will be included in the grouped list. To get the last found item, you need to put the list in reverse order before comparing the items. In SQL, you do that with a subquery.

3. Something to study:

Code: Select all

SELECT *, TIMESTAMPDIFF(HOUR, dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM table
# +----+--------+---------------------+-----------+-----------+
# | id | ticket | dateupdated         | updatedby | age_hours |
# +----+--------+---------------------+-----------+-----------+
# |  1 |      1 | 2014-05-01 00:00:00 | customer  |       754 |
# |  2 |      1 | 2014-05-02 00:00:00 | admin     |       730 |
# |  3 |      3 | 2014-05-03 00:00:00 | customer  |       706 |
# |  4 |      3 | 2014-05-03 00:00:00 | admin     |       706 |
# |  5 |      3 | 2014-05-04 00:00:00 | customer  |       682 |
# |  6 |      6 | 2014-05-05 00:00:00 | customer  |       658 |
# |  7 |      7 | 2014-05-29 00:00:00 | customer  |        82 |
# |  8 |      7 | 2014-05-30 00:00:00 | admin     |        58 |
# |  9 |      7 | 2014-05-31 00:00:00 | customer  |        34 |
# | 10 |     10 | 2014-05-31 00:00:00 | customer  |        34 |
# +----+--------+---------------------+-----------+-----------+

SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM table ORDER BY dateupdated DESC) s
JOIN table t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 48
# +----+--------+---------------------+-----------+-----------+
# | id | ticket | dateupdated         | updatedby | age_hours |
# +----+--------+---------------------+-----------+-----------+
# |  5 |      3 | 2014-05-04 00:00:00 | customer  |       682 |
# |  6 |      6 | 2014-05-05 00:00:00 | customer  |       658 |
# +----+--------+---------------------+-----------+-----------+
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

This is quite something - I've added it to the system, just altering minor things, and making the results sortable, and it's very clever indeed.

Using this method, how would I do this sort of query, without the date checks, just to find those where "t.updatedby = 'customer'" ?
ie. Do a GROUP BY, but only where the final row of the "rows in each group" actually have customer in them?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Trying to get this to work per web site. It's not erroring, but is showing 5 entries, when there are only 4 that are over 48 hours.

I think I may have the website =:website in the wrong place??

Code: Select all

$query = "
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL AND website =:website ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 48";
      $result = $pdo->prepare($query);
      $result->execute(array(':website' => $rowweb->website));
      $num_rows = $result->rowCount();
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Code: Select all

SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT * FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'support'
AND age_hours > 168
Now trying to established those tickets where support was the last to update it, but the customer hasn't responded .... 7 days or more old.
There are definitely some in my local system like that, but this query isn't showing them, nor is it erroring.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Apologies, it is working. Thanks to anyone who does check.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Celauran wrote:You can't just paste console output directly into your code. Try this.

Code: Select all

$query = "SELECT subject, message, firstname, lastname FROM tickets WHERE datetrashed IS NULL AND dateclosed IS NULL AND dateupdated IS NULL";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
	$start_date = new DateTime($row->dateupdated);
	$end_date = new DateTime();
	$interval = $end_date->diff($start_date);
	$hours = (24 * $interval->d) + $interval->h;
	echo "$row->firstname $hours<br/>";
}
How do I do this, but in mins instead?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

It's for a callback service.
Someone enters "15-30 minutes" into a box, I want to add 15 minutes to the time they raised their query ($row->dateraised).
And then calculate the time remaining until the call is due.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Love it when I crack it myself of course:

Code: Select all

      if ($row->calltime == "within 15 mins") 
{
$minutes_to_add = 5;
}

if ($row->calltime == "15-30 minutes") 
{
$minutes_to_add = 15;
}

if ($row->calltime == "1 hour") 
{
$minutes_to_add = 60;
}

$time = new DateTime($row->dateraised);
$time->add(new DateInterval('PT' . $minutes_to_add . 'M'));

$stamp = $time->format('Y-m-d H:i');    

$datetime1 = new DateTime($row->dateraised);
$datetime2 = new DateTime($stamp);
$interval = $datetime1->diff($datetime2);
$hours   = $interval->format('%h'); 
$minutes = $interval->format('%i');
echo 'Call in: '.($hours * 60 + $minutes);
echo " minutes";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Whatever I set $minutes_to_add to, it uses that as $minutes further down:

Code: Select all

      if ($row->calltime == "within 15 mins") 
{
$minutes_to_add = 5;
}

if ($row->calltime == "15-30 minutes") 
{
$minutes_to_add = 15;
}

if ($row->calltime == "1 hour") 
{
$minutes_to_add = 60;
}

$time = new DateTime($row->dateraised);
$time->add(new DateInterval('PT' . $minutes_to_add . 'M'));

$stamp = $time->format('Y-m-d H:i:s');    
echo "$stamp<br/>";
$datetime1 = new DateTime($row->dateraised);
$datetime2 = new DateTime($stamp);
$interval = $datetime1->diff($datetime2);
$hours   = $interval->format('%h'); 
$minutes = $interval->format('%i');

echo 'Call in:  '.($hours * 60 + $minutes);
echo " minutes";
I'm testing using this calltime: 15-30 minutes
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I check hours between two date/times ?

Post by Celauran »

You're adding $minutes_to_add, then asking for the difference between the new time and the original time. What behaviour were you expecting?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

I'm using $minutes_to_add to add that amount of minutes to $row->dateraised into the $stamp time.
And then asking how many between between $stamp and NOW.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I check hours between two date/times ?

Post by Celauran »

No you aren't. You're asking for the difference between that and $row->dateraised.

Code: Select all

$datetime1 = new DateTime($row->dateraised);
$datetime2 = new DateTime($stamp);
$interval = $datetime1->diff($datetime2);
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Yes.
So Date Raised is when they submitted it.
$stamp is that, plus the minutes they want to be called in.

That does work!
It's the bit below, where I am asking how many mins between $stamp and $dateraised that's failing.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

Gotchya!
Spotted it.

$datetime1 = new DateTime($stamp);
$datetime2 = new DateTime($todaydate);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I check hours between two date/times ?

Post by simonmlewis »

How do I tell if it is a "minus" figure?
I can query "$minutes" to adjust the display accordingly, but how do I say "if it's a minus... enter 'time has now passed' ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply