我有以下查询,它从日期范围内获取数据。在调试它时,我遇到了一个特殊的问题:
如果表只包含有今天日期的记录,则不会显示.
不过,
如果我添加了昨天的记录,那么昨天和今天的记录就会显示出来。
我遗漏了什么?
MySQL查询:
/*
SELECT GROUP BY UTL_ACTION | SUM UTL_DURATION
*/
SELECT USER_ID,
UTL_ACTION,
SUM(UTL_DURATION) AS UTL_DURATION_TOTAL
FROM (
SELECT A.PK_USER_TIME_LOG_ID,
A.CLIENT_ID,
A.PROJECT_ID,
A.USER_ID,
A.UTL_DTSTAMP,
/* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
A.UTL_LATITUDE,
A.UTL_LONGITUDE,
A.UTL_EVENT,
A.UTL_ACTION,
/* DURATION in seconds */
TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM tbl_user_time_log A
INNER JOIN tbl_user_time_log B
ON B.PK_USER_TIME_LOG_ID = ( A.PK_USER_TIME_LOG_ID + 1 )
WHERE A.USER_ID = '465605'
/* Between current pay period Start date and Current pay period end date */
/* First day of the week is Monday.*/
AND ( A.UTL_DTSTAMP BETWEEN '2018-01-22' AND '2018-01-28' )
/* Filter out Clock Out. */
AND A.UTL_EVENT <> 'CLOCK OUT'
ORDER BY A.PK_USER_TIME_LOG_ID ASC
) AS tbl_derived_1
GROUP BY UTL_ACTION ASC;桌子..。

期望的结果..。

发布于 2018-01-28 16:58:36
使用带有日期/时间类型的between是个坏主意。虽然对于Server而不是MySQL,亚伦伯特兰的博客帖子解释得很好。
您可能可以通过执行以下操作来修复您的逻辑:
(A.UTL_DTSTAMP >= '2018-01-22' AND
A.UTL_DTSTAMP < '2018-01-29'
)请注意,第二个比较使用的是<,而不是<=和比较,而是多了一天。
https://stackoverflow.com/questions/48488806
复制相似问题