本文介绍了时间维度填充问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的MySql表返回的是天而不是时间.我每天至少需要几分钟的时间,因此应该自动填充1440条记录,但我一直都在等待几天.知道为什么吗?我也不需要秒,只需要小时和分钟,但是我不确定如何不用秒完成日期填充,因为时间的格式始终是"00:00:00"

My MySql table is returning days instead of time. I need a minimum level of minutes in a day so 1440 records should be auto populated but i keep getting days back. Any idea why? Also i dont need seconds, only hours and minutes but i am not sure how to do a date fill without seconds since the format of time is always '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');

推荐答案

我没有正在运行的MySQL实例要测试,但是我相信您应该将currentdate类型更改为DATETIME.

I don't have a running instance of MySQL to test, but I believe you should change your currentdate type to DATETIME.

这篇关于时间维度填充问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-19 23:06