mysql 检索当前和最后一小时之间的差异 [英] mysql retrieve difference between current and last hour

问题描述

对于下表，我想获得每个站点的 col-D 和 col-E 的最后一小时和当前小时之间的差异.作为其中的一部分，我试图首先获取每个站点的最新(当前)小时条目，但以下查询仅列出了 endTime 为 01:00:00 的条目，当我有条目到上午 9 点时

For the below table, i would like to get the difference between last hour and current hour for col-D and col-E for each of the site. As part of that I am trying to first get the latest (current) hour entries for each of the site, but the following query is only listing me the entries with endTime as 01:00:00, when i have entries upto 9.00AM

select distinct(mmeName), endDate, endTime, c_ratio, set_time from attach where
 type='INIT' and Date(endDate)=curDate() and 
Time(endTime) >= DATE_ADD(Time(endTime), INTERVAL -1 HOUR) group by mmeName;

对于当前问题以及找出当前和上一小时之间的差异，我们将不胜感激.

Any help would be appreciated for the immediate issue and as well finding the difference between current and last hour.

推荐答案

已编辑

我想这就是你要找的.这将为您提供 endTime 为每个 mmeName 的最新当前时间之前一小时的任何记录.'max' 子选择获取每个 mmeName 的最新结束日期时间，并在此之前一小时准确匹配记录.

I think this is what you are looking for. This will give you any records where the endTime is one hour prior to the latest current time for each mmeName. The 'max' sub-select gets the latest end datetime for each mmeName, and the join back matches on record exactly one hour prior to that.

SELECT  mmeName, endDate, endTime, c_ratio, set_time 

FROM    attach a

        JOIN 
        (SELECT  mmeName, CONCAT(endDate, ' ' , endTime) max_endDateTime
         FROM    attach 
         WHERE   type = 'INIT' 
         ORDER BY endDate DESC, endTime DESC
        ) AS max ON max.mmeName = a.mmeName
                AND max.max_endDateTime = DATE_ADD(CONCAT(endDate, ' ' , endTime), INTERVAL 1 HOUR)

WHERE   type = 'INIT'
;

原创

select  mmeName, endDate, endTime, c_ratio, set_time 

from    attach 

where   type='INIT' and Date(endDate)=curDate() and 
        endTime >= DATE_SUB(now(), INTERVAL -1 HOUR) 

group by mmeName;

注意:如果给定的 mmeName 有多个匹配的记录，此查询将只获取其中之一.

Note: If there are multiple matching records for a given mmeName, this query will just grab one of them.

EDITED:您需要从 WHERE 子句中删除 TIME() 函数.两者都有日期和时间，如果没有，如果您在上午 12:00 到凌晨 1:00 之间运行它，它将不会返回任何结果.

EDITED: You need drop the TIME() functions from the WHERE clause. Both would have the date and time and if you didn't, if you ran it between 12:00 AM to 1:00 AM it would not return any results.

这篇关于mysql 检索当前和最后一小时之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！