时间维度填充问题

Question

我的 MySql 表返回的是天而不是时间。我每天需要最少的分钟数，因此应该自动填充 1440 条记录，但我不断地返回几天。知道为什么吗？另外，我不需要秒，只需要小时和分钟，但我不确定如何在没有秒的情况下填充日期，因为时间格式始终为“00:00:00”

程序如下：

DELIMITER //
CREATE PROCEDURE p_sc_time(IN startdate DATETIME,IN stopdate DATETIME)
BEGIN
DECLARE currentdate DATE;
SET currentdate = startdate;
WHILE currentdate < stopdate DO
    INSERT INTO sc_time VALUES (
    NULL,
    TIME(currentdate),
    HOUR(currentdate),
    MINUTE(currentdate));
SET currentdate == ADDDATE(currentdate,INTERVAL 1 MINUTE);
END WHILE;
END
//
DELIMITER ;
TRUNCATE TABLE sc_time;
CALL p_sc_time('01-01-01 00:00:00','01-01-01 23:59:59');

Answer 1

我没有正在运行的 MySQL 实例来测试，但我相信您应该将当前日期类型更改为 DATETIME。

Answer 2

DROP TABLE IF EXISTS timedim;

CREATE TABLE timedim  (
    time_id INT NOT NULL auto_increment,
    fulltime time,
    hour int,
    minute int,
    second int,
    ampm varchar(2),
    PRIMARY KEY(time_id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;

CREATE TABLE IF NOT EXISTS --#Must be a non-temporary table b/c temp tables cannot be self joined.
  ints ( i INT ) AS #(Use select+union for CTAS - Create Table)
      SELECT (0) i UNION SELECT (1) UNION SELECT (2) UNION SELECT (3) UNION SELECT (4)
UNION SELECT (5) UNION SELECT (6) UNION SELECT (7) UNION SELECT (8) UNION SELECT (9);

DROP PROCEDURE IF EXISTS timedimbuild;

CREATE PROCEDURE timedimbuild()
BEGIN

DECLARE v_full_date DATETIME;

CREATE TEMPORARY TABLE filler(id INT NOT NULL);

INSERT INTO filler(id)
SELECT (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) id
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 86399 --# 86,400 seconds in a day (0 to 86399)
ORDER BY 1;

SET v_full_date = '1970-01-01 00:00:00';        

INSERT INTO timedim (
    fulltime ,
    hour ,
    minute ,
    second ,
    ampm
) SELECT
     TIME(DATE_ADD(v_full_date, INTERVAL t.id SECOND)), 
     HOUR(DATE_ADD(v_full_date, INTERVAL t.id SECOND)), 
     MINUTE(DATE_ADD(v_full_date, INTERVAL t.id SECOND)), 
     SECOND(DATE_ADD(v_full_date, INTERVAL t.id SECOND)), 
     DATE_FORMAT(DATE_ADD(v_full_date, INTERVAL t.id SECOND), '%p')      
  FROM (SELECT id FROM filler) t; 

DROP TEMPORARY TABLE filler;
END;

call timedimbuild();