减去2 datetime在mysql中(一种以24小时格式，一种以am/pm格式) [英] subtract 2 datetime in mysql ( one in 24 hours format and one in am/pm format )

问题描述

我正在尝试使用mysql创建查询.

I'm trying to create a query using mysql.

select ID,NCOde,ifnull(EndTime,now())-starttime from xxx where starttime between 
'2012-05-09 00:00:00' and '2012-05-09 23:59:59' 

问题是ifnull(EndTime，now())以24小时格式返回日期时间，而开始时间则使用am/pm格式.

the problem is ifnull(EndTime,now()) return datetime in 24 hours format, while the starttime using am/pm format.

我尝试使用 DATE_FORMAT(starttime，'％m-％d-％Y％T')，但是似乎该操作将datetime类型更改为其他类型.

I've tried using DATE_FORMAT(starttime, '%m-%d-%Y %T'), but it seems that the operation changed the datetime type to other type.

有什么建议吗?

推荐答案

使用

Use STR_TO_DATE() to convert your starttime string to a MySQL DATETIME:

STR_TO_DATE(starttime, '%m-%d-%Y %r')

，然后使用 TIMEDIFF() 减去两次:

and then use TIMEDIFF() to subtract two times:

select ID,NCOde,
  TIMEDIFF(ifnull(EndTime,now()), STR_TO_DATE(starttime, '%m-%d-%Y %r'))
from xxx
where STR_TO_DATE(starttime,'%m-%d-%Y %r')
        between '2012-05-09 00:00:00' and '2012-05-09 23:59:59'

您可能应该考虑将 starttime 列的数据类型更改为 TIMESTAMP .还要注意，这假定 EndTime 已经是这种数据类型，否则您也必须对其执行类似的转换.

You should probably consider changing the data type of the starttime column to DATETIME or TIMESTAMP. Note also that this assumes EndTime is already of such a data type, or else you will also have to perform a similar conversion with it too.

这篇关于减去2 datetime在mysql中(一种以24小时格式，一种以am/pm格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！