MySQL DAYOFWEEK() - 我的一周从星期一开始
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1202919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL DAYOFWEEK() - my week begins with monday
提问by picca
I'm using DAYOFWEEK()function in MySQL which returns 1for sunday. But in my country the week starts with monday, not sunday. Is there any chance to get dayofweek from MySQL formated like: (1 - Monday, 2 - Tuesday, ...) ?
我在 MySQL 中使用DAYOFWEEK()函数，它为sunday返回1。但在我的国家，一周从星期一开始，而不是星期日。有没有机会从 MySQL 中获取 dayofweek 格式如下： (1 - Monday, 2 - Tuesday, ...) ？
回答by nos
Use WEEKDAY()instead of DAYOFWEEK(), it begins on Monday.
使用WEEKDAY()而不是DAYOFWEEK()，它从星期一开始。
If you need to start at index 1, use or WEEKDAY() + 1.
如果您需要从索引 1 开始，请使用 或WEEKDAY() + 1。
回答by picca
回答by Justin Giboney
How about subtracting one and changing Sunday
减一改星期天怎么样
IF(DAYOFWEEK() = 1, 7, DAYOFWEEK() - 1)
Of course you would have to do this for every query.
当然，您必须为每个查询执行此操作。
回答by webDEVILopers
You can easily use the MODE argument:
您可以轻松使用 MODE 参数：
MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
MySQL :: MySQL 5.5 参考手册 :: 12.7 日期和时间函数
If the mode argument is omitted, the value of the default_week_format system variable is used:
如果省略 mode 参数，则使用 default_week_format 系统变量的值：
MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables
回答by codemonkey
Could write a udf and take a value to tell it which day of the week should be 1 would look like this (drawing on answer from John to use MOD instead of CASE):
可以写一个 udf 并取一个值来告诉它一周中的哪一天应该是 1 看起来像这样（借鉴 John 的回答以使用 MOD 而不是 CASE）：
DROP FUNCTION IF EXISTS `reporting`.`udfDayOfWeek`;
DELIMITER |
CREATE FUNCTION `reporting`.`udfDayOfWeek` (
_date DATETIME,
_firstDay TINYINT
) RETURNS tinyint(4)
FUNCTION_BLOCK: BEGIN
DECLARE _dayOfWeek, _offset TINYINT;
SET _offset = 8 - _firstDay;
SET _dayOfWeek = (DAYOFWEEK(_date) + _offset) MOD 7;
IF _dayOfWeek = 0 THEN
SET _dayOfWeek = 7;
END IF;
RETURN _dayOfWeek;
END FUNCTION_BLOCK
To call this function to give you the current day of week value when your week starts on a Tuesday for instance, you'd call:
例如，要调用此函数以在您的一周从星期二开始时为您提供当前的星期几值，您可以调用：
SELECT udfDayOfWeek(NOW(), 3);
Nice thing about having it as a udf is you could also call it on a result set field like this:
将它作为 udf 的好处是你也可以在结果集字段上调用它，如下所示：
SELECT
udfDayOfWeek(p.SignupDate, 3) AS SignupDayOfWeek,
p.FirstName,
p.LastName
FROM Profile p;
