以 3 分钟的步长插入时间戳值 [英] insert timestamp values in 3 minute steps

问题描述

我想将 3 分钟步长(给定开始和结束日期)的时间戳值插入到只有一个时间戳列的表中.

I would like to insert timestamp values of 3 minute steps (given a start and end date) into a table with just one timestamp column.

我目前从 sql server 切换到 mysql.我会知道如何在 SQL 中为前一个数据库执行此操作，但不确定后者.谢谢.

I currently switch from sql server to mysql. I would know how to do it for the former database in SQL but am not sure about the latter. Thanks.

这是我目前(不工作)悲惨的尝试:

This is my current (not working) miserable attempt:

DELIMITER //
CREATE PROCEDURE test()
begin
    declare StartTS TIMESTAMP DEFAULT null;
    declare EndTS TIMESTAMP DEFAULT null;

    set StartTS = '2012-01-01 00:00:00';
    set EndTS = '2014-01-01 00:00:00';

    start transaction;
      while StartTS < EndTS do
        insert into timestamps (TimeStampEntry) values (StartTS);
        SET StartTS  = StartTS  + INTERVAL 3 MINUTE;
      end while;
      commit;
end
//

DELIMITER ;

call test();

附注:

create table TimeStamps (
        Id INTEGER NOT NULL AUTO_INCREMENT,
       TimeStampEntry DATETIME,
       primary key (Id)
    )

当前进程:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `myProc`(IN t1 timestamp, IN t2 timestamp)
BEGIN

  WHILE t1 <= t2 DO
INSERT INTO timestamps (TimeStampEntry) Values (t1)  ;
    SET t1 = DATE_ADD(t1, INTERVAL 180 SECOND);
    #SET t1 = t1 + INTERVAL 180 SECOND;
  END WHILE;
END

调用:

CALL myProc('2010-01-01 00:00:00', '2014-01-01 00:00:00');

推荐答案

尝试这样的事情:

CREATE PROCEDURE myProc(IN t1 timestamp, IN t2 timestamp)
BEGIN

  WHILE t1 <= t2 DO
INSERT INTO test (ts) Values (t1)  ;
    SET t1 = DATE_ADD(t1, INTERVAL 3 MINUTE);
  END WHILE;
END;


CALL myProc(now(), ADDDATE(NOW(), INTERVAL 15 MINUTE));

http://sqlfiddle.com/#!2/5672e/9

或更改您的线路:

SET StartTS  = StartTS  + INTERVAL 3 MINUTE;

到

SET StartTS  = ADDDATE(StartTS, INTERVAL 3 MINUTE);

这篇关于以 3 分钟的步长插入时间戳值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！