Page 3 of 4

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

Posted: Sun Jun 01, 2014 11:43 am
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 |
# +----+--------+---------------------+-----------+-----------+

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

Posted: Mon Jun 02, 2014 3:34 am
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?

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

Posted: Mon Jun 02, 2014 5:34 am
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();

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

Posted: Fri Jun 20, 2014 7:23 am
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.

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

Posted: Fri Jun 20, 2014 8:38 am
by simonmlewis
Apologies, it is working. Thanks to anyone who does check.

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

Posted: Wed Jul 09, 2014 6:23 am
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?

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

Posted: Wed Jul 09, 2014 6:25 am
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.

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

Posted: Wed Jul 09, 2014 6:31 am
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";

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

Posted: Wed Jul 09, 2014 7:12 am
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

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

Posted: Wed Jul 09, 2014 7:21 am
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?

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

Posted: Wed Jul 09, 2014 7:27 am
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.

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

Posted: Wed Jul 09, 2014 7:32 am
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);

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

Posted: Wed Jul 09, 2014 7:36 am
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.

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

Posted: Wed Jul 09, 2014 8:20 am
by simonmlewis
Gotchya!
Spotted it.

$datetime1 = new DateTime($stamp);
$datetime2 = new DateTime($todaydate);

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

Posted: Wed Jul 09, 2014 8:25 am
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' ??