MySQL可以替代Oracle的NEXT_DAY函数吗? [英] What is the MySQL alternative to Oracle's NEXT_DAY function?

问题描述

NEXT_DAY("01-SEP-95"，"FRIDAY")返回下一个星期五的日期，但是在MySQL中似乎没有出现此功能.有什么选择?

解决方案

我将用另一种方法将我的帽子扔进戒指:

我有点迟来地意识到，所讨论的Oracle函数将字符串作为第二个参数，因此这并不完全符合要求.但是，MySQL已经将0-6定义为星期一-星期日，无论如何，我还是有道德上的反对意见，建议将字符串用作此类事情的参数.字符串可以来自用户输入或，也可以来自更高级别代码中的数字和字符串值之间的另一个映射.为什么不传递整数?:)

  CREATE FUNCTION`fnDayOfWeekGetNext`(p_date DATE，p_weekday TINYINT(3))退货日期开始RETURN DATE_ADD(p_date，INTERVAL p_weekday-WEEKDAY(p_date)+(ROUND(WEEKDAY(p_date)/(p_weekday + WEEKDAY(p_date)+1))* 7)DAY);结尾 

要细分确定 INTERVAL 值的部分:

方程式的第一部分只是获得指定的工作日与指定日期的工作日之间的偏移量:

  p_weekday-WEEKDAY(p_date) 

如果 p_weekday 大于 WEEKDAY(p_date)，它将返回一个正数，反之亦然.如果它们相同，将返回零.

ROUND()段用于确定相对于日期(> p_date ).因此，举例来说...

  ROUND(WEEKDAY('2019-01-25')/(6 + WEEKDAY('2019-01-25')+1)) 

..返回 0 ，表示本周未发生星期日( 6 )，因为 2019-01-25 是星期五.同样...

  ROUND(WEEKDAY('2019-01-25')/(2 + WEEKDAY('2019-01-25')+1)) 

...返回 1 ，因为星期三( 2 )已经过去.请注意，如果 p_weekday 与 p_date 的工作日相同，则它将返回 0 .

然后将这个值( 1 或 0 )乘以常量 7 (一周中的天数).

因此，如果当前一周已发生 p_weekday ，则它将在偏移量 p_weekday-WEEKDAY(p_date)中加上7，因为该偏移量为负数我们希望将来有个约会.

如果 p_weekday 在当前一周尚未发生，则我们可以将偏移量添加到当前日期，因为偏移量将为正数.因此， ROUND(...)* 7 部分等于零，并且实质上被忽略.

我对这种方法的期望是通过数学方法模拟 IF()条件.这将同样有效:

  RETURN DATE_ADD(p_date，INTERVAL p_weekday-WEEKDAY(p_date)+ IF(p_weekday-WEEKDAY(p_date)< 0，7，0)DAY); 

出于客观性的考虑，在运行1M迭代几次每个功能时，基于 IF 的版本比基于 ROUND 的版本平均快约4.2％

NEXT_DAY("01-SEP-95","FRIDAY") returns what is the date on next Friday, but in MySQL this function does not seem to be appear. What is the alternative?

解决方案

I'm going to throw my hat in the ring with yet another approach:

Edit: I realize somewhat belatedly that the Oracle function in question takes a string as the second argument, and so this doesn't precisely fit the requirement. However MySQL has already kindly defined 0 - 6 as Monday - Sunday, and anyway I have moral objections to using a string as an argument for this type of thing. A string would either come from user input or yet another mapping in higher level code between numeric and string values. Why not pass an integer? :)

CREATE FUNCTION `fnDayOfWeekGetNext`(
        p_date DATE,
        p_weekday TINYINT(3)
        ) RETURNS date
BEGIN

        RETURN DATE_ADD(p_date, INTERVAL p_weekday - WEEKDAY(p_date) + (ROUND(WEEKDAY(p_date) / (p_weekday + WEEKDAY(p_date) + 1)) * 7) DAY);

END

To break the portion down that determines the INTERVAL value:

The first part of the equation simply gets the offset between the weekday specified and the weekday of the date specified:

p_weekday - WEEKDAY(p_date)

This will return a positive number if p_weekday is greater than WEEKDAY(p_date) and vice versa. Zero will be returned if they're the same.

The ROUND() segment is used to determine whether the requested day of the week (p_weekday) has already occurred in the current week relative to the date (p_date) specified. So, by example...

ROUND(WEEKDAY('2019-01-25') / (6 + WEEKDAY('2019-01-25') + 1))

..returns 0, indicating that Sunday (6) has not occurred this week, as 2019-01-25 is a Friday. Likewise...

ROUND(WEEKDAY('2019-01-25') / (2 + WEEKDAY('2019-01-25') + 1))

...returns 1 because Wednesday (2) has already passed. Note that this will return 0 if p_weekday is the same as the weekday of p_date.

This value (either 1 or 0) is then multiplied by the constant 7 (the number of days in a week).

Hence if p_weekday has already occurred in the current week, it will add 7 to the offset p_weekday - WEEKDAY(p_date), because that offset would be a negative number and we want a date in the future.

If p_weekday has yet to occur in the current week, then we can just add the offset to the current date because the offset will be a positive number. Hence the section ROUND(...) * 7 is equal to zero and, in essence, ignored.

My desire for this approach was to simulate an IF() condition mathematically. This would be equally valid:

RETURN DATE_ADD(p_date, INTERVAL p_weekday - WEEKDAY(p_date) + IF(p_weekday - WEEKDAY(p_date) < 0, 7, 0) DAY);

And in the interest of objectivity, in running 1M iterations a few times of each function the IF-based version averaged about 4.2% faster than the ROUND-based version.

这篇关于MySQL可以替代Oracle的NEXT_DAY函数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！