Administrace komentářů

Když si vytvořím testovací data:

CREATE TABLE t (
  id int(11) NOT NULL auto_increment,
  d datetime DEFAULT NULL,
  data varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
);

delimiter $$
CREATE PROCEDURE prepare_test_data()  
BEGIN    
DECLARE 
  i INT DEFAULT 0; 
  WHILE i < 8000 DO
     INSERT INTO t (d, data) VALUES (DATE_FORMAT(NOW() - INTERVAL i MINUTE,
           '%Y-%m-%d-%H:%i:00'),CONCAT('VAL:',i));
     INSERT INTO tt (d, data) VALUES (DATE_FORMAT(NOW() - INTERVAL i MINUTE,
           '%Y-%m-%d-%H:%i:00'),CONCAT('VAL:',i));      
  SET i = i + 12;    
END WHILE;  
END$$
delimiter ;
call prepare_test_data();
DROP PROCEDURE prepare_test_data;

CREATE TABLE series (
  id int(11) NOT NULL auto_increment,
  PRIMARY KEY (id)
);

delimiter $$
CREATE PROCEDURE prepare_series_data()  
BEGIN    
DECLARE 
  i INT DEFAULT 0; 
  WHILE i < 4320 DO
     INSERT INTO series () VALUES();      
  SET i = i + 1;    
END WHILE;  
END$$
delimiter ;
call prepare_series_data();
DROP PROCEDURE prepare_series_data;

Pokud jsou data již ukládána ze sekundama: 00 (je zvolen interval od NOW())

SELECT IFNULL(t.d,DATE_FORMAT(NOW() - INTERVAL series.id MINUTE,'%Y-%m-%d-%H:%i:00')) AS dt, t.data FROM series  
   LEFT JOIN t ON t.d = CONVERT(DATE_FORMAT(NOW() - INTERVAL series.id MINUTE,'%Y-%m-%d-%H:%i:00'),DATETIME)
       ORDER BY series.id ASC;

Pokud NEjsou data již ukládána ze sekundama: 00 (je zvolen interval od NOW())

SELECT IFNULL(t.d,DATE_FORMAT(NOW() - INTERVAL series.id MINUTE,'%Y-%m-%d-%H:%i:00')) AS dt, t.data FROM series  
   LEFT JOIN t ON CONVERT(DATE_FORMAT(t.d - INTERVAL series.id MINUTE,'%Y-%m-%d-%H:%i:00'),DATETIME) 
                  = CONVERT(DATE_FORMAT(NOW() - INTERVAL series.id MINUTE,'%Y-%m-%d-%H:%i:00'),DATETIME)
       ORDER BY series.id ASC;

Efektivní, ale odstranit duplicitu už efektivně neumím:

(SELECT d, data FROM t WHERE d > NOW() - INTERVAL 4320 MINUTE) 
   UNION
(SELECT DATE_FORMAT(NOW() - INTERVAL series.id MINUTE,'%Y-%m-%d %H:%i:00') AS d, NULL AS data FROM series)
  ORDER BY d ASC;

A nakonec uklidím:

DROP TABLE t;
DROP TABLE series;