mysql - 在 MySQL 中更改日期时间值

Question

我想做的是设置时间间隔的开始，如果它没有正确设置到存储过程中的话。然而，它在某种程度上并不能很好地工作..

这是我的代码:

CREATE PROCEDURE intervals_generator (IN start DATETIME, IN ending DATETIME, IN intervalis INT)
BEGIN

    -- temp values
    DECLARE next_date DATETIME;

    -- result temp values
    DECLARE start_temp DATETIME;
    DECLARE ending_temp DATETIME;

    -- date formatting variables
    DECLARE year CHAR(20);
    DECLARE month CHAR(20);
    DECLARE day CHAR(20);

    DECLARE new_start CHAR(20);

    -- SET starting date if is incorrect  DATE_FORMAT(NOW(), '%d %m %Y')
    SET year := DATE_FORMAT(start, '%Y');
    SET month := DATE_FORMAT(start, '%c');
    SET day := DATE_FORMAT(start, '%e');

    IF intervalis = '1_day' THEN
        BEGIN
            SET new_start := year+' '+month+' '+day+' 00:00:00';
        END;
    ELSEIF intervalis = '1_month' THEN
        BEGIN
            SET new_start := year+' '+month+' 1 00:00:00';
        END;
    ELSEIF intervalis = '1_quarter' THEN
        BEGIN
            IF MONTH(start) IN (2, 3) THEN
                SET month := 1;
            ELSEIF MONTH(start) IN (5, 6) THEN
                SET month := 4;
            ELSEIF MONTH(start) IN (8, 9) THEN
                SET month := 7;
            ELSEIF MONTH(start) IN (11, 12) THEN
                SET month := 10;
            END IF;
            SET new_start := year+' '+month+' 1 00:00:00';
        END;
    ELSEIF intervalis = '1_year' THEN
        BEGIN
            SET new_start := year+' 1 1 00:00:00';
        END;
    END IF;
    SET start := STR_TO_DATE(new_start, '%Y %c %e %h:%i:%s');

    SELECT year, month, day, start;
    DROP TEMPORARY TABLE IF EXISTS intervals_result;

END//

DELIMITER ;

我尝试了很多不同的格式设置和功能，但输出仍然是错误的，像这样:

mysql> CALL intervals_generator('2013-02-01 00:00:00', '2015-12-31 00:00:00', '1_year');
+------+-------+------+---------------------+
| year | month | day  | start               |
+------+-------+------+---------------------+
| 2013 | 2     | 1    | 2016-00-00 00:00:00 |
+------+-------+------+---------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

我真的不明白为什么输出是“2016-00-00”而不是“2013-01-01”。年、月和日变量被定义为 CHAR，并且从日期时间中提取它们的函数也应该返回 CHAR。而函数STR_TO_DATE也应该是CHAR格式，所以这对我来说是个谜。

如果有人有什么想法，请给我提示。

Answer 1

如果你在 DATEs 而不是字符串中工作，你可以使用 MySQL 的日期 functions and operators 并使一切变得更简单......但不要太简单，因为这是 MySQL。

MySQL 和日期的问题在于它的日期功能是一个真正的大杂烩，有时与 DATE 一起工作，有时与字符串一起工作，有时与整数一起工作，并且缺少基本功能。它缺少设置日期的简单功能；无法将 DATE 的 MONTH 部分更改为二月。甚至没有根据年、月和日确定日期的好方法，您得到的最接近的结果是 MAKEDATE()，它需要一年和一年中的某一天(？!)。 Fortunately, DATEs in MySQL respond to math operations，这比弄乱字符串要好。

例如，如果您有 2013-02-12 并且想要 2013-02-01，您必须首先使用 MAKEDATE 创建一个仅包含年份的新日期, 然后添加月份部分。

-- 2013-01-01
SET new_date := MAKEDATE(YEAR(old_date), 1);

-- 2013-02-01
-- Since MONTH returns from 1 to 12, you need to take away one.
SET new_date := new_date + (INTERVAL MONTH(old_date) - 1) MONTH;

在删除所有未使用的变量、更改为日期数学并使用 CASE statement 而不是大的 IF/ELSE 链之后，我们得到:

CREATE PROCEDURE intervals_generator (IN start_date DATE, IN intervals TEXT)
BEGIN
    DECLARE new_start DATE;

    CASE intervals
        WHEN '1_day' THEN
            -- Nothing to do, DATE has already truncated the time portion.
            SET new_start := start_date;
        WHEN '1_month' THEN
            -- Set to the year and month of the start date
            SET new_start := MAKEDATE(YEAR(start_date), 1) + INTERVAL (MONTH(start_date) - 1) MONTH;
        WHEN '1_quarter' THEN
        BEGIN
            -- Set to the year and month of the start date
            SET new_start := MAKEDATE(YEAR(start_date), 1) + INTERVAL (MONTH(start_date) - 1) MONTH;
            -- Subtract the necessary months for the beginning of the quarter
            SET new_start := new_start - INTERVAL (MONTH(new_start) - 1) % 3 MONTH;
        END;
        WHEN '1_year' THEN
            -- Set the date to the first day of the year
            SET new_start := MAKEDATE(YEAR(start_date), 1);
    END CASE;

    SELECT new_start;
END//

Try it out.