Page 1 of 4
How do I check hours between two date/times ?
Posted: Wed May 28, 2014 9:24 am
by simonmlewis
[text]23 May 2014 12:30:42 [/text]
'datetime' field.
Records it as:
So how do I say: $hours = $todaydate - $entrydate?
Bearing in mind I do want to calculate it down to hours, and not just days.
It's so we can spot if a message has not been replied to for 48 hours.
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 9:41 am
by Celauran
DateTime::diff
Code: Select all
php > $start_date = new DateTime('2014-05-28 10:00:00');
php > $end_date = new DateTime('2014-05-29 11:30:00');
php > $interval = $end_date->diff($start_date);
php > print_r($interval);
DateInterval Object
(
[y] => 0
[m] => 0
[d] => 1
[h] => 1
[i] => 30
[s] => 0
[weekday] => 0
[weekday_behavior] => 0
[first_last_day_of] => 0
[invert] => 1
[days] => 1
[special_type] => 0
[special_amount] => 0
[have_weekday_relative] => 0
[have_special_relative] => 0
)
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 9:45 am
by simonmlewis
So how do I extract just the hours number from that? And use it in a variable query?
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 9:51 am
by Celauran
It's a property of the object that you can access directly. You'll also need to convert days to hours.
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 9:57 am
by simonmlewis
Ok well I don't know how to access what you are suggesting - this is why I am asking.
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 10:02 am
by Celauran
There's a print_r of the object above.
Code: Select all
$hours = (24 * $interval->d) + $interval->h;
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 10:03 am
by Celauran
Note that that will break once you start getting into intervals of months or years. You could use the same approach to write something more robust pretty easily should the need arise.
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 10:58 am
by simonmlewis
I'm trying to get it basically just showing the hours between each message now.
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');
php > $end_date = new DateTime('$todaydate');
php > $interval = $end_date->diff($start_date);
php > print_r($interval);
DateInterval Object
(
[y] => 0
[m] => 0
[d] => 1
[h] => 1
[i] => 30
[s] => 0
[weekday] => 0
[weekday_behavior] => 0
[first_last_day_of] => 0
[invert] => 1
[days] => 1
[special_type] => 0
[special_amount] => 0
[have_weekday_relative] => 0
[have_special_relative] => 0
)
$hours = (24 * $interval->d) + $interval->h;
echo "$row->firstname $hours<br/>";
}
[text]Parse error: syntax error, unexpected 'Object' (T_STRING) in C:\xampp\phpMyAdmin\site\includes\a_tickets.inc on line 30[/text]
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:04 am
by Celauran
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/>";
}
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:13 am
by simonmlewis
Firstly, my bad. firstname is not in that DB table, however, with the below, it shows just 0, where one of them should be at least 2 hours.
Code: Select all
$query = "SELECT subject, ticket, dateupdated 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->ticket $hours<br/>";
}
[text]26064 0
27584 0
25301 0
10986 0
9223 0[/text]
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:17 am
by Celauran
Have you checked that the DateTime objects are correct? I don't know what your input is. Have you inspected the DateTimeInterval object? Does it look OK?
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:18 am
by Celauran
Oh well no wonder!
... AND dateupdated IS NULL
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:26 am
by simonmlewis
Idiot!!!
Ok, I've changed that to be dateupdated IS NOT NULL AND updatedby = 'customer'.
But this goes back to a problem I have raised elsewhere here.
I want to know what the LAST entry of a ticket number is, and when it is updatedby = 'customer'.
so if you have:
Ticket: 123 ID: 5 Updatedby: customer
Ticket: 123 ID: 6 Updatedby: admin
I don't want that to find anything, because 'admin' has replied.
But if it was like this:
Ticket: 123 ID: 5 Updatedby: customer
Ticket: 123 ID: 6 Updatedby: admin
Ticket: 123 ID: 7 Updatedby: customer
Yes, I want ID:7 to be spotted.
How?
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:38 am
by Celauran
I'd select the MAX(id), group by ticket, and check updatedby afterward in PHP. Seems easiest.
Re: How do I check hours between two date/times ?
Posted: Wed May 28, 2014 11:42 am
by simonmlewis
But then that is going to find the wrong entry. For example, if it finds $row->updatedby to be "admin", but the last one is actually "customer".... echoing "$row->updatedby" will produce "admin" incorrectly..............
Why can it not say "find the max entry for ticketid, ONLY if it equals "customer" ??