当前位置：theitroad>MySQL 如何将星期数转换为日期？

MySQL 如何将星期数转换为日期？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/7078730/
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

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 20:52:25 　来源:igfitidea点击:

How to convert number of week into date?

mysqlsqldatetime

提问by Cephalopod

Given a year and calendar week, how can I get the tuesday of that week as a date?

给定一年和日历周，我如何获得该周的星期二作为日期？

采纳答案by nre

Given you have yearand cw(calender week) as variables (e.g. from a SELECT statement) you can get the DATE as following:

鉴于您有year和cw（日历周）作为变量（例如来自 SELECT 语句），您可以获得如下日期：

DATE_SUB(
  DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK),
  INTERVAL WEEKDAY(
    DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)
  ) -1 DAY),

The phrase DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)is duplicated; did not want to store a variable. The SQL-Statement worked nicely for me on MySQL.

该短语DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)重复；不想存储变量。SQL 语句在 MySQL 上非常适合我。

UPDATE:Just for clarification: WEEKDAY(DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK))will yield the first day of the week. Substracting a number from it (-1 for Tuesday; -2 for Wednesday and so forth will select a specific day in the week for you). See here.

更新：只是为了澄清：WEEKDAY(DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK))将产生一周的第一天。从中减去一个数字（-1 代表星期二；-2 代表星期三等等，将为您选择一周中的特定日期）。见这里。

回答by indago

In MySQL the STR_TO_DATE()function can do the trick in just one line!

在 MySQL 中，该STR_TO_DATE()函数只需一行即可完成！

Example: We want to get the date of the Tuesdayof the 32thweek of the year 2013.

例如：我们希望得到的日期Tuesday的中32日年度的一周2013。 

SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W');

would output:

会输出：

'2013-08-13'

I think this is the best and shortest solution to your problem.

我认为这是解决您问题的最佳和最短的解决方案。

回答by Ethan Joffe

The definitions of calendar week I found all said "a period of seven consecutive days starting on Sunday".

我发现日历周的定义都说“从星期日开始连续7天的时间段”。

The following is MySQL specific... your mileage may vary...

以下是特定于 MySQL 的……您的里程可能会有所不同……

DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK) adds the weeks from the 1st of the year which is not correct...

DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK) 从一年的第一天开始添加周数，这是不正确的......

mysql> select DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK);
+----------------------------------------------+
| DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK) |
+----------------------------------------------+
| 2011-01-08                                   |
+----------------------------------------------+

By this definition, it is only meaningful to have the calendar week range from 1-53, and have this represent the Sunday of that week. As such, we would add 2 days to the nth Sunday of the year to get Tuesday.

根据这个定义，只有日历周的范围从 1 到 53 才有意义，并且让 this 代表该周的星期日。因此，我们将在一年中的第 n 个星期日加上 2 天以获得星期二。

The following gets the date of the first sunday of the year...

以下获取一年中第一个星期日的日期...

mysql> select date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY);
+------------------------------------------------------------------------+
| date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY) |
+------------------------------------------------------------------------+
| 2012-01-02                                                             |
+------------------------------------------------------------------------+

so this will get the date of the 10th sunday (note interval 9 week since we are already at 1)...

所以这将得到第 10 个星期日的日期（注意间隔 9 周，因为我们已经在 1）...

mysql> select date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week);
+-----------------------------------------------------------------------------------------------------+
| date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week) |
+-----------------------------------------------------------------------------------------------------+
| 2010-03-07                                                                                          |
+-----------------------------------------------------------------------------------------------------+

add 2 more days to get to tuesday...

再过两天就到星期二了……

mysql> select date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day);
+--------------------------------------------------------------------------------------------------------------------------------+
| date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day) |
+--------------------------------------------------------------------------------------------------------------------------------+
| 2010-03-09                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------+

or more generally:

或更一般地说：

select 
date_add( 
    date_add( 
        date_add('<year>-01-01', interval (8 - dayofweek('<year>-01-01')) % 7 DAY) 
        , interval <week-1> week)
    , interval <dayOfWeek> day
);

回答by Warren Landis

In looking at indago's answerand then doing a bunch of tests, I was getting the followingweek as the results.

在查看indago 的答案然后进行一系列测试时，我得到了下一周的结果。

I've made a minor adjustment, and the dates then matched:

我做了一个小调整，然后日期匹配：

SELECT STR_TO_DATE('2019 1 Monday', '%x %v %W') -- beginning of week

SELECT STR_TO_DATE('2019 1 Sunday', '%x %v %W') -- end of week

You can compare the results with here.

您可以将结果与此处进行比较。

回答by Nae

DELIMITER $$
CREATE FUNCTION fn_yearweek_to_date(
    var_yearweek INTEGER UNSIGNED,
    var_weekday ENUM(
        'Monday',
        'Tuesday',
        'Wednesday',
        'Thursday',
        'Friday',
        'Saturday',
        'Sunday'
        )
    )
RETURNS DATE DETERMINISTIC
BEGIN
    RETURN STR_TO_DATE(CONCAT(var_yearweek, var_weekday), '%x%v%W');
END;
DELIMITER ;

SELECT
    fn_yearweek_to_date(YEARWEEK(NOW(), 1), 'Sunday'),
    fn_yearweek_to_date(YEARWEEK(NOW(), 1), 7)
;

回答by Date-Shark

Given solutions doesn't consider, that the first week of a year may start at the end of december. So we must check, if January 1st belongs to calendarweek of old or new year:

鉴于解决方案不考虑，一年的第一周可能从 12 月底开始。所以我们必须检查，如果 1 月 1 日属于旧年或新年的日历周：

SET @week=1;
SET @year=2014;
SET @x_weeks_after_new_year=DATE_ADD(MAKEDATE(@year, 1), INTERVAL (SELECT IF(WEEKOFYEAR(MAKEDATE(@year, 1))>50 , 0 , -1))+@week WEEK);
SELECT
  CONCAT(@year, '-', @week) WeekOfYear,
  @weekStart:=DATE_SUB(@x_weeks_after_new_year, INTERVAL WEEKDAY(@x_weeks_after_new_year) DAY) Monday,
  DATE_ADD(@weekStart, INTERVAL 6 DAY) Sunday

This will result in:

这将导致：

+------------+------------+------------+
| WeekOfYear |   Monday   |   Sunday   |
+------------+------------+------------+
|   2014-1   | 2013-12-30 | 2014-01-05 |
+------------+------------+------------+

回答by CrazyMPh

Here is a sample that might help:

这是一个可能有帮助的示例：

SET DATEFIRST 1
declare @wk int  set @wk = 33
declare @yr int  set @yr = 2011

select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 2 -
     datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) as date

and the result is:

结果是：

2011-08-16 00:00:00.000

which is today (Tuesday).

这是今天（星期二）。

回答by obesechicken13

I think it'd be easier to write the logic of the function using php.

我认为使用php编写函数的逻辑会更容易。

If you use a php script, you can put all dates in a format similar to "day-month-year" and use a loop to go through every day (from 1980s to 2038 or from your mysql dates column).

如果您使用 php 脚本，您可以将所有日期放入类似于“日-月-年”的格式中，并使用循环遍历每一天（从 1980 年代到 2038 年或您的 mysql 日期列）。

http://www.php.net/manual/en/function.date-format.php

http://www.php.net/manual/en/function.date-format.php

Then use date format on the dates in that loop to convert them to the days of the week.

然后在该循​​环中的日期上使用日期格式将它们转换为星期几。

Here is a listing of things that can be used in date formats. http://www.php.net/manual/en/function.date.phpD N l w all help you with day of the week.

这里列出了可以在日期格式中使用的内容。http://www.php.net/manual/en/function.date.phpD N l 都可以帮助您处理星期几。

回答by David

Well theoretically you could use DATEPART with the dw parameter to get to find the first tuesday of the month and then add 7*[CalenderWeek] to get the appropriate date

那么理论上你可以使用 DATEPART 和 dw 参数来找到该月的第一个星期二，然后添加 7*[CalenderWeek] 以获得适当的日期

http://msdn.microsoft.com/en-us/library/ms174420.aspx

http://msdn.microsoft.com/en-us/library/ms174420.aspx

回答by user1007717

The upvoted solution worked for me in 2014 and 2015 but did not work for me in 2016 (possibly because the start of the Year is on Monday and not on Sunday.

已投票的解决方案在 2014 年和 2015 年对我有用，但在 2016 年对我不起作用（可能是因为年初是星期一而不是星期日。

I used the following function to correct this:

我使用以下函数来纠正这个问题：

STR_TO_DATE( CONCAT(mod(day_nr + 1 ,7) , '/', week_nr, '/', year), '%w/%u/%Y')

STR_TO_DATE( CONCAT(mod(day_nr + 1 ,7) , '/', week_nr, '/', year), '%w/%u/%Y')

In my data : day_nr = 0 -> Monday,

在我的数据中：day_nr = 0 -> 星期一，

day_nr = 6 -> Sunday

day_nr = 6 -> 星期日

So I had to fix that with a mod function

所以我不得不用一个 mod 函数来解决这个问题

相关推荐

MySQL MySQL查询返回包含空格的行

MySQL MySQL查询返回包含空格的行

MySQL 每天12:00:00到18:00:00之间按时间选择记录

MySQL 每天12:00:00到18:00:00之间按时间选择记录

MySQL 数据库错误：不允许大于 max_allowed_pa​​cket 的数据包 - 在插入期间

MySQL 数据库错误：不允许大于 max_allowed_pa​​cket 的数据包 - 在插入期间

一个查询中的 MySQL 更新条件（UPDATE、SET 和 CASE）

一个查询中的 MySQL 更新条件（UPDATE、SET 和 CASE）

相关推荐
最近更新
标签