如何计算PHP / MySQL中多行上两列之间的时差

I have this PHP Code:

我有这个PHP代码:

    $sql="SELECT ticket_seq, SUM(TIMEDIFF(timeend, timestart)) as total FROM ticket_updates GROUP BY ticket_seq";
$rs=mysql_query($sql,$conn) or die(mysql_error());
if(mysql_num_rows($rs) > 0)
{
    while($result=mysql_fetch_array($rs))
    {
        $sql2="SELECT * from tickets where ticketnumber = '".$result["ticket_seq"]."' ";
        $rs2=mysql_query($sql2,$conn) or die(mysql_error());
        $result2=mysql_fetch_array($rs2);

        $sql3="SELECT * from customer where sequence = '".$result2["company"]."' ";
        $rs3=mysql_query($sql3,$conn) or die(mysql_error());
        $result3=mysql_fetch_array($rs3);

        if($result["total"] > $result3["support_hours"])
        {
            echo $result3["company"].' - '.$result["total"].'<br>';
        }
    }
}

but then when i echo $result["total"] i just get random numbers.

但是当我回显$ result [“total”]时,我只是得到随机数。

I am trying to select the time difference between timestart and timeend for multiple rows and add it together.

我正在尝试为多行选择timestart和timeend之间的时差,并将它们添加到一起。

2 个解决方案

#1

I think something like this will solve your issue

我认为这样的事情可以解决你的问题

SELECT SUM(
   (SELECT TIMEDIFF(timeend, timestart) FROM ticket_updates GROUP BY ticket_seq)
)

#2

Quick play and something like this might do it:-

快速播放和类似的事情可能会这样做: -

$sql="SELECT ticket_updates.ticket_seq, customer.company, SUM(CAST((UNIX_TIMESTAMP(timeend) - UNIX_TIMESTAMP(timestart)) AS UNSIGNED)) as total 
    FROM ticket_updates 
    INNER JOIN tickets ON tickets.ticketnumber = ticket_updates.ticket_seq
    INNER JOIN customer ON customer.sequence = tickets.company
    GROUP BY ticket_updates.ticket_seq, customer.company
    HAVING total > (customer.support_hours*60*60)";
$rs=mysql_query($sql,$conn) or die(mysql_error());
if(mysql_num_rows($rs) > 0)
{
    while($result=mysql_fetch_array($rs))
    {
            echo $result3["company"].' - '.$result["total"].'<br>';
    }
}

This is converting the date/time fields to unix timestamps, finding the diffence and casting it to unsigned and then checking that against the support hours multiplied by 60 and 60 (ie, converting from hours to seconds)

这是将日期/时间字段转换为unix时间戳,找到差异并将其转换为无符号,然后检查支持小时数乘以60和60(即从小时转换为秒)

#1