How to check if a date is a specific day in the week in MySQL?-CodePudding

I have a mysql table presence with a row

id: 1
uid: 14
start: 2021-11-01 00:00:00
end: 2021-12-31 00:00:00
repetitive: 1

This row gives me the information, that the user 14 is present every Monday from 2021-11-01 to 2021-12-31.

SELECT 1 FROM presences
WHERE 2021-11-15 BETWEEN DATE(`start`) AND DATE(`end`)

checks if the given date (2021-11-15) is between his presence dates but how can I add logic to check for "every Monday"? Additionally, if end is null, it should check for every Monday in future without end. Monday is given by start date and can be every other day as well. repetitive gives me to check that given day by start. If repetitive is 0, it should just check like my query already does, if the date is between start and end.

Human query: Get all rows from presence where given date is between start and end, if end is not null and where given day is day of start.

CodePudding user response：

Get all rows from presence where given date is between start and end, if end is not null and where given day is day of start.

could be humanly translated to:

SELECT *
FROM t
WHERE '2021-11-15' >= `start`
AND (
    '2021-11-15' <= `end` OR
    `end` IS NULL
)
AND (
    repetitive = 0 OR
    repetitive = 1 AND WEEKDAY('2021-11-15') = WEEKDAY(`start`)
)

CodePudding user response：

Directly answering the question posed in the title of your post you can use:

DAYOFWEEK(date): Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.
WEEKDAY(date): Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).
DAYNAME(date): Returns the name of the weekday for date. The language used for the name is controlled by the value of the lc_time_names system variable

But in your description of the query you want, you say

Get all rows from presence where given date is between start and end, if end is not null and where given day is day of start. (my emphasis)

which implies you want the number of instances of start date day name/index in your range. To do this you can use different methods to extrapolate your range to rows and the pick the required day from it.

A calendar table would be best, as you can already build in the day name/index in to the data in the table.

SELECT COUNT(*) as monday_count
FROM presences JOIN calendar
  ON calendar.day_date BETWEEN DATE(`start`) AND DATE(`end`)
WHERE `end` IS NOT NULL AND calendar.day_name = DAYNAME(`start`);

Here's an example of building a calendar table, it's MSSQL specific so some of the queries might need to be modified for MySQL, but you get the idea.