计算日期时间MYSQL

Question

我需要一个SQL命令来完成这个任务。我的来源数据是：

日期；W1 2014-05-02 :00:10；2500 2014-05-02 :00:11；2800 2014-05-02 :00:12；2900 2014-05-02 :00:13；1000 2014-05-02 :00:14；1000 2014-05-02 :00:10；1000 2014-05-02 :00:11；1000 2014-05-02 :00:12；1000 2014-05-02 :00:13；1000 2014-05-02 :00:14；1000 2014-05-02 :00:10；3000 2014-05-02 :00:11；2500 2014-05-02 :00:12；2000 2014-05-02 :00:13；2500 2014-05-02 :00:14；2800

因此，我现在想要的是W1高于2000年的日期差

应该是：

2014-05-02 :00:10 -> 2014-05-02 :00:12 2014-05-02 08:00:10 -> 2014-05-02 :00:14

我知道我可以用php来完成这个任务，但是是否有一种方法可以用sql命令来完成呢？

谢谢你的帮助

编辑1:或者说我至少得到了这样的东西：

2014-05-02 03:00:10 2014-05-02 03:00:12 2014-05-02 08:00:10 2014-05-02 03:00:14

每次超过1秒之间。

Answer 1

我想，如果按日期和小时(分钟除外)对行进行分组，然后在存在多个时间的情况下计数不同的日期，这将非常简单，这满足了秒差条件，因此可以使用min/max从'%Y-%m-%d %H'组获取日期。

SELECT 
MIN(`Date`) `min_date`,
MAX(`Date`) `max_date`,
CONCAT(MIN(`Date`)  ,'->',MAX(`Date`) ) `concatenated`,
COUNT(DISTINCT `Date`) `different_dates`
FROM t
WHERE `W1` > 2000
GROUP BY DATE_FORMAT(`Date`,'%Y-%m-%d %H')
HAVING different_dates >1 

小提琴演示

Answer 2

我认为create table w1data (atime DATETIME, value int)和LOAD DATA INFILE很容易。

在此之后，您有一个表，它可以由

select * from w1data where value > 2000

这不是你想要的，而是一个好的开始。

现在要比较select的两个不同行，这需要一个kond of a技巧：

set @lastrow = NULL;
select (if(@lastrow is NULL, NULL, unix_timestamp(atime) - unix_timestamp(@lastrow)) as diff_seconds, @lastrow := atime as dummy
from (select distinct atime from w1data where value > 2000 order by atime)
;

编辑:如果您想要的是时间而不是秒，只需使用(EDIT2：when和限制词when替换) (EDIT3: instead当然需要一个别名)

set @lastrow = NULL;
select concat(@lastrow, " -> ", atime) as length, (@lastrow := atime) as dummy
from (select distinct atime from w1data where value > 2000 order by atime) as dat_sel
;

Answer 3

这里有一种方法可以得到指定的结果，

SELECT CONCAT(
         SUBSTRING_INDEX(SUBSTRING_INDEX(t.state,'#',2),'#',-1)
       ,' -> ',
         SUBSTRING_INDEX(SUBSTRING_INDEX(t.state,'#',3),'#',-1)
       ) AS `r`
  FROM ( SELECT IF(@state+0,
                  IF(w1>2000, NULL, CONCAT(@state := 0,'#', @start, '#', @prev_dt , '#')),
                  IF(w1>2000, CONCAT(@state := 1,'#',@start := dt,'#'), NULL)
                ) AS state
              , @prev_dt := m.dt AS dt
           FROM mydata m
          CROSS
           JOIN (SELECT @start := '', @prev_dt := '', @state := '0') i
          ORDER BY m.dt, m.w1 
       ) t
 WHERE t.state LIKE '0#%'
   AND SUBSTRING_INDEX(SUBSTRING_INDEX(t.state,'#',3),'#',-1) 
     > SUBSTRING_INDEX(SUBSTRING_INDEX(t.state,'#',2),'#',-1)

这使用MySQL用户定义的变量“保存”前几行的值。内联视图查询(别名为t)负责检查w1的值，并确定范围是否已经启动或结束。

外部查询只选择范围标记为"ended“的行。(这只返回已结束的范围，也就是说，如果最后一行的值为w1>2000，则范围尚未标记为and，包含该行的范围将被省略。

添加一个额外的“跟踪”记录(即"UNION ALL SELECT MAX(n.dt+INTERVAL 1 SECOND AS dt, 0 AS w1 FROM mydata n"将解决这个问题)。或者，列表可以按反向顺序处理，从最近的日期回到最早的日期，反转检查，然后省略一个未启动的“最早”范围。)

这种方法适用于任何长度的“范围”，其跨度为几秒，或多小时或数天(只要范围结束)。

安装测试用例：

CREATE TABLE mydata (dt DATETIME, w1 INT UNSIGNED) ENGINE=MYISAM;

CREATE INDEX mydata_IX1 ON mydata (dt, w1);

INSERT INTO mydata (dt, w1) VALUES
('2014-05-02 03:00:10','2500')
,('2014-05-02 03:00:11','2800')
,('2014-05-02 03:00:12','2900')
,('2014-05-02 03:00:13','1000')
,('2014-05-02 03:00:14','1000')
,('2014-05-02 05:00:10','1000')
,('2014-05-02 05:00:11','1000')
,('2014-05-02 05:00:12','1000')
,('2014-05-02 05:00:13','1000')
,('2014-05-02 05:00:14','1000')
,('2014-05-02 08:00:10','3000')
,('2014-05-02 08:00:11','2500')
,('2014-05-02 08:00:12','2000')
,('2014-05-02 08:00:13','2500')
,('2014-05-02 08:00:14','2800');