mysql时间间隔为12小时

Question

我正试图在我的时隙查询中找出一个bug。突破冲头是在下午12点，突破冲头是在中午12:30。我在mysql中使用timediff计算差异。我将突破时间后的突破时间传递给timediff函数。它正在返回正确的分钟，但它也返回-12小时。当我在timediff函数中交换突破和突破时，它会返回一个正数的12小时，并得到正确的分钟。

我想可能有人想看看这个查询。警告:它又大又丑。查询：

SELECT CONCAT(pe.first, ' ', pe.last) AS Name,
               U.EmpID AS 'Empl ID',
               DATE_FORMAT(U.`time`, '%m-%d-%Y') AS 'Punch Date',
               DATE_FORMAT(U.`time`, '%W') AS 'Weekday',
               TIME_FORMAT(SEC_TO_TIME(SUM(U.delta)), '%H:%i') AS 'Time Worked',
               TIME_FORMAT(
                  TIMEDIFF(
                     (SELECT PunchDateTime
                        FROM timeclock_punchlog tp
                       WHERE     PunchEvent = 'breakout'
                             AND DATE(tp.PunchDateTime) = DATE(U.time)
                             AND tp.EmpID = U.EmpID), 
                     (SELECT PunchDateTime
                        FROM timeclock_punchlog tp
                       WHERE     PunchEvent = 'breakin'
                             AND DATE(tp.PunchDateTime) = DATE(U.time)
                             AND tp.EmpID = U.EmpID)),
                  '%h hrs, %i min.')
                  AS Lunch
                FROM ((SELECT `enter`.EmpID,
                           `enter`.PunchDateTime AS `time`,
                           DATE_FORMAT(`enter`.PunchDateTime, '%m-%d-%Y')
                              AS 'Punch Date',
                           TIMESTAMPDIFF(SECOND,
                                         `enter`.PunchDateTime,
                                         '2003-05-01 00:00:00')
                              AS `delta`
                      FROM timeclock_punchlog AS `enter`
                     WHERE `enter`.`In-Out` = 1)
                   UNION
                   (SELECT `leave`.EmpID,
                           `leave`.PunchDateTime AS `time`,
                           DATE_FORMAT(`leave`.PunchDateTime, '%m-%d-%Y')
                              AS 'Punch Date',
                           -TIMESTAMPDIFF(SECOND,
                                          `leave`.PunchDateTime,
                                          '2003-05-01 00:00:00')
                              AS `delta`
                      FROM timeclock_punchlog AS `leave`
                     WHERE `leave`.`In-Out` = 0)) AS U
               LEFT JOIN prempl pe ON u.EmpID = pe.prempl
        WHERE DATE(U.`time`) >= "2015-10-05"
        AND DATE(U.`time`) <= "2015-10-07"
        AND U.EmpID = 0349
        GROUP BY date(U.`time`), EmpID
        ORDER BY U.EmpID, U.`time` ASC

下面是一个SQL Fiddle，它用一个简单的查询来再现问题。

insert into times values 
    ('12:30 pm','12:00 pm'),
    ('12:00 pm','12:30 pm');

select time_format(timediff(start,end),'%h hrs, %i min.') from times

结果：

12 hrs, 30 min.
-12 hrs, 30 min.

下面是使用datetime类型的sql花键，并产生相同的结果。http://sqlfiddle.com/#!9/9fb73/1

Answer 1

问题是在格式化时间的方式；看看差异是负面的，你会陷入这样的麻烦。

此外，time_format几乎没有限制，我建议阅读它的MySQL文档。

根据上一次通信，我假设您使用的是datetime数据类型，那么下面的查询就可以工作了。

CREATE TABLE `times` (
  `PunchID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `breakout` datetime NOT NULL,
  `breakin` datetime NOT NULL,
  PRIMARY KEY (`PunchID`)
);

insert into `times` 
values (1,'2015-10-06 12:00:00', '2015-10-06 12:30:00');

选择查询(注意格式字符串h更改为H)

select time_format(timediff(breakin,breakout),'%H hrs, %i min.') from times;

一切都正常。

如果您不确定哪一列可能比执行timediff和格式化的其他列具有更大的价值，那么请给您一点建议，它可以给您提供奇怪的结果，在这种情况下最好使用UNIX_TIMESTAMP()，并处理整数数学。

关于MySQL日期和时间函数。的文档

Answer 2

不能将像'12:30 pm'这样的时间字符串直接输入MySQL。am / pm部件将始终被忽略。尝试使用24小时格式代替。