How to build a mysql query that checks date and time being between two values?-CodePudding

I want to build a specific calendar feature.

As a basement I have the following MySQL table:

CREATE TABLE `presence` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `eid` int(10) unsigned DEFAULT NULL,
  `start` timestamp NULL DEFAULT NULL,
  `end` timestamp NULL DEFAULT NULL,
  `frequence` int(11) DEFAULT NULL,
  `fulltime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

with the following example data:

INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (1, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (2, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (3, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (4, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (5, 14, "2021-11-29 10:00:00", NULL, 1, 1);

In my app environment I have the following examples:

row id#1 means, that an employee (eid: 14) is present every day from 2021-11-29 to 2021-12-13. Each day from 10 am to 6 pm.

row id#2 means, that an employee (eid: 14) is present every monday from 2021-11-29 to 2021-12-13. Each Monday from 10 am to 6 pm. Monday because start is a Monday and because frequence = 1.

row id#3 means, that an employee (eid: 14) is present every monday from 2021-11-29 to 2021-12-13. Each Monday the entire day, because fullday = 1. Thus, ignoring the time.

row id#4 means, that an employee (eid: 14) is present every day from 2021-11-29 to 2021-12-13. Each day the entire day, because fullday = 1 tells again to ignore the time.

row id#5 finally has an open end for the presence. It means the employee is present every Monday from 2021-11-29 until infinity.

I am working on the following query the entire day and I'm at stage where I can't tell what I expect anymore...

SET @date = "2021-12-13 19:00:00";
SET @fullday = 0;


SELECT start, end, frequence, fulltime
FROM `presence`
WHERE (
    IF (@fullday = 1, 
        DATE(@date) >= DATE(`start`)
        ,
        DATE(@date) >= DATE(`start`) AND
        TIME(@date) >= TIME(`start`)
    )
)
AND (
    IF (@fullday = 1, 
        DATE(@date) >= DATE(`end`)
        ,
        DATE(@date) >= DATE(`end`) AND
        TIME(@date) >= TIME(`end`)
    )
)
AND (
    `frequence` IS NULL OR
    `frequence` = 1 AND WEEKDAY(@date) = WEEKDAY(`start`)
)

In my app I will have a php function employee_is_present(int $eid, string $date): bool where $date can be either Y-m-d H:i:s or just Y-m-d, to check if an employee is generally available on a day (not having a time) or at a Very specific time. This is what I try to mimic using SET @fullday = 0|1;

CodePudding user response：

According to the input, date could be either with or without time, it is easier to separate these two situations. We can do that since we are using function call to query

Checking date only could use this query

SET @eid =14; 
SET @date = DATE("2021-12-13");
SELECT *
FROM presence
WHERE @date >= DATE(`start`) AND ( `end` IS NULL OR @date <= DATE(`end`))
AND (`frequence` IS NULL OR (`frequence` = 1 AND WEEKDAY(`start`) = WEEKDAY(@date)))
AND eid = @eid

Checking with time, we use the above query result and filter the time

SET @eid =14; 
SET @date = DATE("2021-12-13 19:00:00");
SET @time = TIME(@date);
SELECT *
FROM (
  SELECT *
  FROM presence
  WHERE @date >= DATE(`start`) AND ( `end` IS NULL OR @date <= DATE(`end`))
  AND (`frequence` IS NULL OR (`frequence` = 1 AND WEEKDAY(`start`) = WEEKDAY(@date)))
  AND eid = @eid
) f
WHERE `fulltime` = 1 OR (TIME(`start`) <= @time AND @time <= TIME(`end`))

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3e207fed77e34a6b458e79764e382156