mysql 当前时间的一周后,MYSQL从当前年份,当前月份和一周中的变量日期获取日期...

我需要通过使用星期几和时间作为唯一的信息获得一系列日期.

我知道这个操作的反向,即使用WEEKDAY(日期)或DAYOFWEEK(日期),但这不是我需要的.

我有一个看起来像这样的表,它将被连续扫描并与当前日期进行比较.

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

| id | weekday | start | end |

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

| 1 | 1 | 09:00:00 | 17:00:00 |

| 2 | 2 | 09:00:00 | 17:00:00 |

| 3 | 3 | 09:00:00 | 17:00:00 |

| 4 | 4 | 23:00:00 | 17:00:00 |

| 5 | 5 | 18:00:00 | 23:00:00 |

| 6 | 6 | 00:00:00 | 00:00:00 |

| 7 | 7 | 00:00:00 | 01:00:00 |

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

以上是简单版本,我希望这个表有数百或数千个可能的迭代.正如在第4排观察到的那样,时间也可能会超过午夜,我必须补偿.

我需要一个查询,它将为我提供以下结果集,并且在按工作日列的当前年份,月份和星期几的日期锁定到日期时遇到问题.

示例结果集：

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

| start | end |

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

| 2013-06-24 09:00:00 | 2013-06-24 17:00:00 |

| 2013-06-25 09:00:00 | 2013-06-25 17:00:00 |

| 2013-06-26 09:00:00 | 2013-06-26 17:00:00 |

| 2013-06-27 23:00:00 | 2013-06-28 17:00:00 | #

| 2013-06-28 18:00:00 | 2013-06-28 23:00:00 |

| 2013-06-29 00:00:00 | 2013-06-30 00:00:00 | #

| 2013-06-30 00:00:00 | 2013-06-30 01:00:00 |

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

任何帮助将不胜感激,我已经接近只获得CURRENT DATE,但是,这给我带来了一些问题,因为这些日期后来需要转换,所以我需要一个包含所有这些日期的数组,而不仅仅是当前天.

编辑：发布我当前的代码：虽然我不相信这是朝着正确的方向前进

SELECT TIMESTAMP(IF (end < start,

CURRENT_DATE,

SUBDATE(CURRENT_DATE, 1)),

start) AS 'start',

TIMESTAMP(IF (end < start,

CURRENT_DATE,

SUBDATE(CURRENT_DATE, 1))

end) AS 'end',

FROM shift

结果如下：

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

| start | end |

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

| 2013-06-27 23:00:00 | 2013-06-28 17:00:00 |

| 2013-06-28 18:00:00 | 2013-06-28 23:00:00 | # Needs the rest of the dates of the week

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

第二次编辑

感谢以下评论者的帮助,我已经得到了下面的询问,但是有些人已经表达了对设计的关注,并且在我找到答案之前我会进行一些测试.如果有更好的方法来完成这项工作,我真的很有兴趣听到它！

我想最好的一点是当月份在查询中途发生变化时,使用星期几确定差异可能会很麻烦.

查询：

SELECT @diff:=(CAST(weekday AS SIGNED) - (WEEKDAY(CURRENT_DATE) + 1)) as 'diff',

@date:=DATE_ADD(CURRENT_DATE, INTERVAL @diff DAY) as 'start_date',

TIMESTAMP(@date, start) AS 'start',

TIMESTAMP(IF (end <= start,

DATE_ADD(@date, INTERVAL 1 DAY),

@date),

end) AS 'end'

FROM shift

结果：

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

| diff | start_date | start | end |

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

| -4 | 2013-06-24 | 2013-06-24 09:00:00 | 2013-06-24 17:00:00 |

| -3 | 2013-06-25 | 2013-06-25 09:00:00 | 2013-06-25 17:00:00 |

| -2 | 2013-06-26 | 2013-06-26 09:00:00 | 2013-06-26 17:00:00 |

| -1 | 2013-06-27 | 2013-06-27 23:00:00 | 2013-06-28 17:00:00 |

| 0 | 2013-06-28 | 2013-06-28 18:00:00 | 2013-06-28 23:00:00 |

| 1 | 2013-06-29 | 2013-06-29 00:00:00 | 2013-06-30 00:00:00 |

| 2 | 2013-06-30 | 2013-06-30 00:00:00 | 2013-06-30 10:00:00 |

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

第三编辑

我已经测试了上述查询在跨越月份开始和结束的时段,并发现它适合其目的,我将上面的查询以及一些注释移到答案中,希望有人可能会发现它有用未来.

在这方面,我还将包括来自各个评论者的各种问题和建议,再次感谢所有花时间提供帮助的人.

解决方法:

好的,所以我解决你的问题的方法是创建一个日期表(它甚至可以是动态创建的临时表).我不是试图通过将日期连接到时间来伪造时间差异,而是实际上在几秒钟内消除差异,并将它们添加到我们正在拉动的第一个DATETIME标记中,将日期与DAYOFWEEK(mydate)的时间连接起来)=工作日. IF语句表示如果小时数相等则使用24小时,否则使用时差的绝对值来计算秒数.

不是火箭科学,而是有点挑战性.

我正在使用的主要SQL是这里(在这里为格式化添加了硬回车)：

select m.mydate,

CONVERT(CONCAT_WS(' ',m.mydate,s.start) USING latin1) as startTime,

if(s.start=s.end,86400,

ABS(MOD(TIME_TO_SEC(s.end)-TIME_TO_SEC(s.start),86400))) as secs,

CONVERT(CONCAT_WS(' ',m.mydate,s.start) + INTERVAL

if(s.start=s.end,86400,

ABS(MOD(TIME_TO_SEC(s.end)-TIME_TO_SEC(s.start),86400)))

second USING latin1) as endTime from StackOverflow.shifts s,

StackOverflow.mydates m where DAYOFWEEK(m.mydate)=s.weekday;

输出如下：

mydate startTime secs endTime

2013-06-27 2013-06-27 18:00:00 18000 2013-06-27 23:00:00

2013-06-28 2013-06-28 00:00:00 86400 2013-06-29 00:00:00

2013-06-29 2013-06-29 00:00:00 3600 2013-06-29 01:00:00

2013-06-30 2013-06-30 09:00:00 28800 2013-06-30 17:00:00

2013-07-01 2013-07-01 09:00:00 28800 2013-07-01 17:00:00

2013-07-02 2013-07-02 09:00:00 28800 2013-07-02 17:00:00

2013-07-03 2013-07-03 23:00:00 21600 2013-07-04 05:00:00

更新

我不是以任何方式提倡这种方法.鉴于可获得的信息,我只是回答原始问题.有时,数据有各种形式,如果他们可以强制要求数据格式,那么就更难以使用数据.虽然最初只是尝试使用一个表似乎是不可能的,但添加日期表(甚至是临时表)使得它变得更加容易.我可以想到的唯一另一个可能出现的问题是夏令时变化,这会增加或减少一小时,这种方法无法解释.

标签：mysql,sql

来源： https://codeday.me/bug/20190709/1409256.html