MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.35


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;
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);
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


id
eid
start
end
frequence
fulltime


1
14
2021-11-29 10:00:00
2021-12-13 18:00:00
null
null

2
14
2021-11-29 10:00:00
2021-12-13 18:00:00
1
null

3
14
2021-11-29 10:00:00
2021-12-13 18:00:00
1
1

4
14
2021-11-29 10:00:00
2021-12-13 18:00:00
null
1

5
14
2021-11-29 10:00:00
null
1
1


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`))


id
eid
start
end
frequence
fulltime


3
14
2021-11-29 10:00:00
2021-12-13 18:00:00
1
1

4
14
2021-11-29 10:00:00
2021-12-13 18:00:00
null
1

5
14
2021-11-29 10:00:00
null
1
1

id	eid	start	end	frequence	fulltime
1	14	2021-11-29 10:00:00	2021-12-13 18:00:00	null	null
2	14	2021-11-29 10:00:00	2021-12-13 18:00:00	1	null
3	14	2021-11-29 10:00:00	2021-12-13 18:00:00	1	1
4	14	2021-11-29 10:00:00	2021-12-13 18:00:00	null	1
5	14	2021-11-29 10:00:00	null	1	1