Как я могу хранить тип интервала MySQL?

Вот что я хотел бы сделать: 



SET @interval_type := MONTH;

SELECT '2012-01-01' + INTERVAL 6 @interval_type;

+------------+

|'2012-06-01'|

+------------+


И, конечно, это не работает, и в MySQL нет типа данных "interval".



Я хочу иметь возможность хранить значение интервала и тип интервала в таблице, чтобы база данных могла быстро выполнять математику естественным образом без необходимости писать большой оператор switch, ala 



... ELSE IF (type = 'MONTH') { SELECT @date + INTERVAL @value MONTH; } ...


Поддерживается ли это каким-либо образом в MySQL или у вас есть умный хак для этого?



Спасибо, ты молодец.

747   3  

3 ответов:

Это решение может пригодиться кому-то, кто реализует очередь заданий для cron или что-то подобное.

Предположим, что у нас есть опорная дата (DATETIME) и интервал повторения. Мы хотели бы сохранить оба значения в базе данных и получить быстрое сравнение, пришло ли уже время выполнить и включить задание в очередь выполнения или нет. Интервал может быть нетривиальным, например (1 год 12 дней 12 часов) и управляется мудрым пользователем (администратором), так что пользователь не будет использовать значения превышение диапазона обычного типа данных DATETIME или иное преобразование должно быть реализовано в первую очередь. (18 МЕСЯЦЕВ - > 1 ГОД 6 МЕСЯЦЕВ).

Затем мы можем использовать тип данных DATETIME для хранения как значений опорной даты, так и интервала. Мы можем определить хранимую функцию, используя: 

DELIMITER $$

CREATE DEFINER=`my_db`@`%` FUNCTION `add_interval`(`source` DATETIME, `interval` DATETIME) RETURNS datetime
BEGIN
  DECLARE result DATETIME;

  SET result = `source`;
  SET result=DATE_ADD(result, INTERVAL EXTRACT(YEAR FROM `interval`) YEAR);
  SET result=DATE_ADD(result, INTERVAL EXTRACT(MONTH FROM `interval`) MONTH);
  SET result=DATE_ADD(result, INTERVAL EXTRACT(DAY FROM `interval`) DAY);
  SET result=DATE_ADD(result, INTERVAL EXTRACT(HOUR FROM `interval`) HOUR);
  SET result=DATE_ADD(result, INTERVAL EXTRACT(MINUTE FROM `interval`) MINUTE);
  SET result=DATE_ADD(result, INTERVAL EXTRACT(SECOND FROM `interval`) SECOND);

RETURN result;
END

Затем мы можем сделать арифметику DATETIME, используя эту функцию, например

// test solution
SELECT add_interval('2014-07-24 15:58:00','0001-06-00 00:00:00');

// get job from schedule table
SELECT job FROM schedule WHERE add_interval(last_execution,repetition)<NOW();

// update date of executed job
UPDATE schedule SET last_execution=add_interval(last_execution,repetition);
4  
2014-07-24 17:09:58

Вы можете решить эту проблему, используя подготовленные операторы, учитывая, что нет никакой языковой конструкции, доступной для использования. Преимущество здесь в том, что вы получаете производительность и гибкость, которые вы хотите; это может быть легко помещено в хранимую процедуру или функцию для дополнительной ценности: 

SET @date = '2012-01-01';
SET @value = 6;
SET @type = 'MONTH';

SET @q = 'SELECT ? + INTERVAL ? ';
SET @q = CONCAT(@s, @type);

PREPARE st FROM @q;
EXECUTE st USING @date, @value;

В качестве альтернативы, в зависимости от архитектуры вашей базы данных / программного обеспечения и типа интервалов даты / времени, о которых вы думаете, вы можете просто решить эту проблему, используя шкалу времени интервал: 

SELECT @date + INTERVAL @value SECOND
  • 1 секунда - 1
  • 1 минута-60
  • 1 час-3600
  • 1 день-86400 (24 часа)
  • 1 неделя-604800 (7 дней)
  • 1 месяц-2419200 (4 недели)
1  
2012-03-20 02:04:39

Вот упрощенный подход. Он работает достаточно быстро. Вы можете изменить порядок операторов switch, чтобы оптимизировать скорость, если вы чувствуете, что будете бить одних чаще, чем других. Я не противопоставил это решению Криса Хатчинсона. Я столкнулся с проблемами, пытаясь обернуть его в хорошую функцию из-за динамического SQL. Во всяком случае, для потомства это гарантированно сработает: 

CREATE FUNCTION AddInterval( date DATETIME, interval_value INT, interval_type TEXT  ) 
RETURNS DATETIME
DETERMINISTIC
BEGIN
    DECLARE newdate DATETIME;
    SET newdate = date;

    IF interval_type = 'YEAR' THEN
        SET newdate = date + INTERVAL interval_value YEAR;
    ELSEIF interval_type = 'QUARTER' THEN
        SET newdate = date + INTERVAL interval_value QUARTER;
    ELSEIF interval_type = 'MONTH' THEN
        SET newdate = date + INTERVAL interval_value MONTH;
    ELSEIF interval_type = 'WEEK' THEN
        SET newdate = date + INTERVAL interval_value WEEK;
    ELSEIF interval_type = 'DAY' THEN
        SET newdate = date + INTERVAL interval_value DAY;
    ELSEIF interval_type = 'MINUTE' THEN
        SET newdate = date + INTERVAL interval_value MINUTE;
    ELSEIF interval_type = 'SECOND' THEN
        SET newdate = date + INTERVAL interval_value SECOND;
    END IF;

    RETURN newdate;
END //

Он поставляется с таким же упрощенным тестом бенчмарка: 

CREATE FUNCTION `TestInterval`( numloops INT ) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE date DATETIME;
    DECLARE newdate DATETIME;
    DECLARE i INT; 
    SET i = 0;

    label1: LOOP
        SET date = FROM_UNIXTIME(RAND() * 2147483647);
        SET newdate = AddInterval(date,1,'YEAR');
        SET i = i+1;
        IF i < numloops THEN
            ITERATE label1;
        ELSE
            LEAVE label1;
        END IF;
    END LOOP label1;
    return i;
END //
0  
2012-03-23 02:45:01

Comments

    Ничего не найдено.
Click here to cancel reply.