issues with DATETIME in database

Question

I have devised the following code; to calculate the waiting time and expected time for a patient. The code should also echo a warning if the patient has been waiting too long.

Please note: Waiting_time is DATETIME in the database.

Here is the code;

<?php

$conn = mysqli_connect("localhost","root","") or die ("No connection");
mysqli_select_db($conn, "a&e") or die('Could not select database.');

$query = "SELECT PatientID, Forename, Surname, Gender, Illness, Priority, Arrival_time,NOW() as now,ABS(TIMEDIFF(NOW(), Arrival_time)) as Waiting_Time FROM Patient";
$result = mysqli_query($conn, $query) or die("Invalid query"); 

echo "<table border='1'>
<tr>
<th>PatientID</th>
<th>Forename</th>
<th>Surname</th>
<th>Gender</th>
<th>Illness</th>
<th>Priority</th>
<th>Waiting Time</th>
</tr>";

while ($row = $result->fetch_object()){

//Select the  expected and discharge time for this patient.
  $query2 = "SELECT Abs(TIMEDIFF(Expected_Time,'00:00:00')) as Expected,Abs(TIMEDIFF(Discharge_Time,'00:00:00')) as Discharge ".
                "FROM priority_time ".
                "WHERE Illness = '".$row->Illness."'".
                    " AND Priority = '".$row->Priority."'".
                ";";

    $result2 = mysqli_query($conn, $query2) or die("Invalid statement: ".$query2);

    $row2 = $result2->fetch_object();
    $expected =  $row2->Expected;

    $discharge  = $row2->Discharge;
    echo "expected-> ".$expected." discharge-> ".$discharge;

    if($expected > $discharge){
        echo "There is a problem with the database consistency, expectedTime must be less than dischargeTime!";
    }
    //Set the patient color.
    if($row->Waiting_Time <  $expected && $row->Waiting_Time <  $discharge){
        echo "<tr>";
    }
    if($row->Waiting_Time >=  $expected && $row->Waiting_Time <  $discharge){
        echo '<tr bgcolor="#FFFF00">';
    }
    if($row->Waiting_Time >  $expected && $row->Waiting_Time >  $discharge){
        echo '<tr bgcolor="#FF0000">';
    }

    //Print patient info
     echo 
      "<td>" . $row->PatientID . "</td>
      <td>" . $row->Forename . "</td>
      <td>" . $row->Surname . "</td>
      <td>" . $row->Gender . "</td>
      <td>" . $row->Illness . "</td>
      <td>" . $row->Priority . "</td>
      <td>" . $row->Waiting_Time . "(".$expected."-".$discharge.") </td>";

    //Close row
    echo "</tr>";

}

echo "</table>";
mysqli_close($conn);
?>

EDIT: On each row, in the column Waiting Time it is showing the waiting time in seconds, and in brackets the currentTime a minus and the arrival time, just for checking. How do I convert the waiting time to the format hh:mm:ss to have a better representation for the user?

Showing;

Waiting time 
01:15:42(10500.000000-10500.000000)

Why is it displaying (10500.000000-10500.000000)?

the output is the issue, a the top its displaying expected-> 10500.000000 discharge-> 10500.000000 on top of the table. — S H, Mar 27 '13 at 12:12
Ok, sry. Thought, you'd just posted that for as as a comparisson — LuigiEdlCarno, Mar 27 '13 at 12:15
Ive asked the question in the bottom edit; How do I convert the waiting time to the format hh:mm:ss to have a better representation for the user? — S H, Mar 27 '13 at 12:18

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Try this:

gmdate("H:i:s", $seconds)

If that doesn't work, have a look at How to convert seconds to time format?

Update:

To do this in the SQL statement try something like this:

SELECT TIME_TO_SEC(TIMEDIFF('2013-03-27 12:00:00', '2013-03-27 10:00:00')) diff;

So something like this:

SELECT PatientID
, Forename
, Surname
, Gender
, Illness
, Priority
, Arrival_time
, NOW() as now
, TIME_TO_SEC(TIMEDIFF(NOW(), Arrival_time)) as Waiting_Time 
FROM Patient;

Then you would change this line:

<td>" . $row->Waiting_Time . "(".$expected."-".$discharge.") </td>

to this:

<td>" . gmdate("H:i:s", $row->Waiting_Time) . "(".$expected."-".$discharge.") </td>

edited May 23 '17 at 12:14

Community

1
1

answered Mar 27 '13 at 12:23

llanato

2,508
6
37
59

what i add this to the SQL statement to convert seconds into h:i:s ? – S H Mar 27 '13 at 12:28
Added in a update to my post with what you can do in the SQL statement. – llanato Mar 27 '13 at 12:33
Edited question to show what this is showing; waiting_time as; 3382(10500.000000-10500.000000) – S H Mar 27 '13 at 12:41
Now you can pass the seconds 3382 into the gmdate() function I've put above and it will give you the time format hh:mm:ss as required. – llanato Mar 27 '13 at 12:47
awesome; its displaying it in hh:mm:ss but also 01:12:23(10500.000000-10500.000000); how do i correct this? – S H Mar 27 '13 at 12:56
What are you looking to correct? A bit more explanation please? – llanato Mar 27 '13 at 13:02
You need to do the same for the two variables $expected & $discharge that is explained above that you just did for $Waiting_Time. – llanato Mar 27 '13 at 13:11
could you edit your example please to show how i would do this – S H Mar 27 '13 at 13:56

score 0 · Answer 2 · answered Mar 27 '13 at 12:29

0

When dealing with dates and times in php strtotime() is your biggest friend. Lucky the date() function is all you need for this. Something that is going to convert seconds to a readable format could be the following.

$time = strtotime('now');
echo $time . '<br />';
$var =  date('H:i:s',$time);
echo $var;

answered Mar 27 '13 at 12:29

Scottzozer

116
9

I would place it just below your DB object fetch. Do it on each time you need and that way you can compare those times with strtotime($convertedTime) > strtotime($otherConvertedTime) and then at the bottom when you echo you can just use those variables. – Scottzozer Mar 27 '13 at 17:02
If your coloring works fine and how you expect it to I would actually just do it right before the echo that way you wont have to change much – Scottzozer Mar 27 '13 at 17:04

issues with DATETIME in database

2 Answers2

Linked