如何在mysql查询Php中计算时间 [英] How to calculate time in mysql query Php

问题描述

我在数据库名称booking"中有下表

id userId taxId serviceStarted duration(in minutes)1 3 2 2019-10-30 13:06:59 20

现在我想知道estimatedTime(服务何时结束)所以我可以使用mysql查询来做到这一点吗?我想要在 mysql 查询中这样的东西

serviceStarted + duration - currentdatetime

在mysql查询中，这可能吗?如果是，那怎么办?

解决方案

此查询返回完成服务还剩多少时间，以小时:分钟:秒为单位:(serviceStarted + duration) - currentdatetime

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, NOW(), ADDTIME(serviceStarted, SEC_TO_TIME(duration*60))))

要获得剩余的分钟数:

SELECT TIMESTAMPDIFF(SECOND, NOW(), ADDTIME(serviceStarted, SEC_TO_TIME(duration*60)))/60

例如，如果 serviceStarted = '2019-10-30 16:35:00' 和 NOW() = '2019-10-30 12:12:49'，结果将是:

282.1833

因此 282.183/60 = 服务将在 4,703055 小时后完成.

<小时>

ADDTIME 添加秒(第二个参数)到日期时间(第一个参数).

`SEC_TO_TIME' 将秒转换为时间.

TIMESTAMPDIFF示例:

<块引用>

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');->3SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');->-1SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');->128885

注意，如果服务已经完成，查询返回负数.

I have following table in database name "booking"

id userId taxiId serviceStarted duration(in minutes)
1   3   2   2019-10-30 13:06:59 20

Now i want to know estimatedTime ( when service will end) so can i do this using mysql query ? i want something like this in mysql query

serviceStarted + duration - currentdatetime

in mysql query,Is this possible ? if yes then how can do this ?

解决方案

This query returns how much time left for complete the service, in hours:minutes:seconds: (serviceStarted + duration) - currentdatetime

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, NOW(), ADDTIME(serviceStarted, SEC_TO_TIME(duration*60)))) 

To get the minutes left:

SELECT TIMESTAMPDIFF(SECOND, NOW(), ADDTIME(serviceStarted, SEC_TO_TIME(duration*60)))/60 

For example, if serviceStarted = '2019-10-30 16:35:00' and NOW() = '2019-10-30 12:12:49', the result will be:

282.1833

therefore 282.183/60 = the service will be completed after 4,703055 hours from now.

---

ADDTIME add the seconds (2nd argument) to the datetime (1st argument).

`SEC_TO_TIME' convert from seconds to time.

TIMESTAMPDIFF examples:

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

Note that if the service is already completed, the query returns a negative amount.

这篇关于如何在mysql查询Php中计算时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！