用于查找时差的 SQL 计算 [英] SQL calculation for finding time difference

问题描述

我已使用 (NOW) 函数获取当前 TIME 并将其作为到达时间"插入到数据库中.

I have used the (NOW) function to get the current TIME and insert into database as "Arrival Time."

如下图所示:

$sql="INSERT INTO Time(Arrival_Time)
VALUES(NOW())";

类型为时间，插入为；

12:17:29

我需要通过(当前时间 - 到达时间)来计算等待时间；

I need to calculate waiting time by (current time - arrival time);

示例:当前时间 (13:15) 到达时间 (12:15)

Example: Current time (13:15) Arrival Time (12:15)

所以这将显示:

Waiting time 
1 hour 

以下是我尝试过的解决方案；

Here are the solutions Ive tried;

<?php

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

$query = "SELECT Arrival_time, TIMEDIFF(Arrival_time,NOW()) as Waiting_Time FROM Time";
$result = mysqli_query($conn, $query) or die("Invalid query");

echo "<table border='1'>
<tr>
<th>Waiting Time</th>
</tr>";

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

 echo "<tr>
  <td>" . $row->Waiting_Time . "</td>
  </tr>";

}

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

编辑

$query = "SELECT Arrival_time, TIME_FORMAT(ABS(TIMEDIFF(CURTIME(), Arrival_time)),'%H hours') as Waiting_Time FROM Time";";

显示

Waiting time 
3 hours 

我将如何使用 time 以便如果；waiting_time 超过 3 小时，提示接下来必须看这个病人！".

How would I use time so that if; waiting_time exceeds 3 hours, alert "This patient must be seen next!".

推荐答案

您可以尝试使用 CURRENT_TIME 或 CURTIME() 代替 NOW():

You can try to use CURRENT_TIME or CURTIME() instead of NOW() :

SELECT Arrival_time, TIMEDIFF(Arrival_time,CURRENT_TIME) as Waiting_Time FROM Time;

因为 NOW() 是 DATETIME，所以它不仅仅是时间.MySQL 文档

Because NOW() is a DATETIME, it's not just the time. MySQL Doc

[添加]
您可以使用 TIME_FORMAT(time_diff,'%H hours') 获取小时数.
并删除-，添加ABS()方法以获取绝对值:

[ADD]
You can use TIME_FORMAT(time_diff, '%H hours') to get hours.
And to remove the -, add ABS() method to get the absolute value :

SELECT Arrival_time, TIME_FORMAT(ABS(TIMEDIFF(Arrival_time,CURRENT_TIME)),'%H hours') as Waiting_Time FROM Time;

[如果 timediff > 3 则更改返回值]

[Change return value if timediff > 3]

SELECT Arrival_time, TIME_FORMAT(ABS(TIMEDIFF(Arrival_time,CURRENT_TIME)),'%H hours left') as Waiting_Time FROM Time WHERE CAST(TIME_FORMAT(ABS(TIMEDIFF(Arrival_time,CURRENT_TIME)),'%H') as UNSIGNED INTEGER)>3

您可以使用 timediff 值制作您想要的所有内容...这里，查询仅返回 diff 大于 3 小时的行...

You can make all you want with the timediff value... Here, The query just return rows with a diff greater than 3 hours...

这篇关于用于查找时差的 SQL 计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！