mysql timediff 关闭 12 小时

Question

我正在尝试找出时钟查询中的错误。闯关时间为中午 12 点，突破时间为中午 12:30。我在 mysql 中使用 timediff 计算差异。我将突破时间和突破时间传递给 timediff 函数。它返回正确的分钟，但它也返回 -12 小时。当我在 timediff 函数中交换 breakin 和 breakout 时，它会返回正值 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 fiddle 并产生相同的结果。 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;

一切正常。

如果您不确定哪个列可能比其他列具有更大的值(value)，请执行 timediff 并格式化它可以给您带来奇怪的结果，最好使用 UNIX_TIMESTAMP() 在这种情况下进行整数数学运算。

有关 MySQL date and time functions. 的文档