MYSQL Get the date from the current year, current month, and a variable day of the week

Question

I need to get a series of dates using the day of the week and time as the only bit of information.

I am aware of the opposite of this operation, which uses WEEKDAY (date) or DAYOFWEEK (date), but this is not what I need.

I have a table that looks like this: it will be continuously checked and compared with the current date.

+----+---------+----------+----------+ | 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 | +----+---------+----------+----------+ 

There was a simple version above, I expect this table to have hundreds or thousands of possible iterations. As noted in the 4th row, times can also change at midnight, and I must compensate.

I need a query that will provide me with the following result sets, and I am having trouble locking the date based on the current year, month, and past day of the week from the day of the week column.

Example result set:

 +---------------------+---------------------+ | 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 | # <---- Notice the date difference here | 2013-06-28 18:00:00 | 2013-06-28 23:00:00 | | 2013-06-29 00:00:00 | 2013-06-30 00:00:00 | # <---- Also here, the span is 24 hours | 2013-06-30 00:00:00 | 2013-06-30 01:00:00 | +---------------------+---------------------+ 

Any help would be appreciated, I got as close to the EXACT DATE as possible, but this creates some problems for me, since these dates should be converted later, so I need an array of them, and not just the current day.

Edit: Added my current code: Although I do not believe that this is quite directed in the right direction

 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 

And the result:

 +---------------------+---------------------+ | 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 +---------------------+---------------------+ 

SECOND EDIT

Thanks to the help of the below commentators, I came to the next request, moving forward, although some of them expressed concern about the design, and I will run some tests before I agree to the answer. If there is a better way to do this, I would be very interested to hear that!

I believe the best moment is that the month changes halfway through the request, using the day of the week to determine what difference can be unpleasant.

Query:

 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 

Result:

 +------+------------+---------------------+---------------------+ | 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 | +------+------------+---------------------+---------------------+ 

Third editor

I tested the above query for periods that span the beginning and end of the month, and found that it was suitable for its purpose. I will translate the above request as well as some notes to the answer in the hope that someone will find this useful in the future.

In this article I will also talk about various errors and recommendations of various commentators, again thanks to everyone who took the time to help.

Answer 1

After he cracked it a bit, I went to the answer below, which I imagine if someone encounters a similar problem in the future. I would like to thank everyone who took the time to help, I found a lot of answers very useful, and I think I listed my conclusions below.

As a result, I received a request:

 SELECT @diff:=(CAST(weekday AS SIGNED) - (WEEKDAY(CURRENT_DATE) + 1)) as 'diff', @date:=DATE_ADD(CURRENT_DATE, INTERVAL @diff DAY) as 'start_date', CONVERT(TIMESTAMP(@date, start) USING latin1) AS 'start', CONVERT(TIMESTAMP(IF (end <= start, DATE_ADD(@date, INTERVAL 1 DAY), @date), end) USING latin1) AS 'end' FROM shift 

A lot of things happen here:

• First, I try to get the difference between the current day of the week and the list shown in the table.
• The "Day of the week" column of my table was actually referenced by UNSIGNED INTEGER, which made it impossible to get the difference in days when that difference was negative.
• The above problem was solved using the CAST function, it had to be distinguished from the SIGNED integer value, which allows us to deny it.
• The @date variable is used only as a placeholder for use elsewhere in the query.
• The TIMESTAMP function returned a byte array in C # instead of string or date data. Thanks to AbsoluteZERO for specifying a conversion to latin1 in which the problems are fixed.

The above query returns the following data, which is the desired result. Since the table is specified in UTC, and the time being checked is then converted by C # to business logic, this is enough to circumvent any problems that may arise with the help of summer savings for such calculations.

The resulting data:

 +------+------------+---------------------+---------------------+ | 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 | +------+------------+---------------------+---------------------+ 

Hope this helps someone.

Answer 2

Okay, so I ran into your problem - create a date table (it could even be a temporary table created on the fly). Instead of trying to fake time differences by combining dates with time, I actually pull the difference in seconds and add them to the first DATETIME label, which we pull out, combining the date with the time when DAYOFWEEK(mydate) = weekday . IF say that if the hours are equal, then use 24 hours, otherwise use the absolute value of the time difference to calculate seconds.

Not rocket science, but a little complicated.

The main SQL I use is (solid returns added here for formatting):

 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; 

The result is as follows:

 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 

Here's the SQL Fiddle .

Update

I do not protect this method in any way. I simply answered the original question, given the information that was available. Sometimes data comes in different forms, and it’s harder to work with than it would be a luxury for one if they could set the data format. Although at first it seemed impossible to just use a single table, adding a date table (or even a temporary table) made it a lot easier. The only other problem that I can think of that may arise is the transition to a change in daylight saving time, which will add or subtract an hour that this method does not take into account.