$['time_posting'] = '02:00,05:00,07:00,08:00';
autoposting_planned
в базе данных, в которой хранятся даты уже запланированных публикаций в столбце datetime
, например:2022-07-13 02:00:00
2022-07-13 05:00:00
2022-07-13 07:00:00
$['time_posting']
и выбирать ближайшую свободную дату (проверяя еще текущее время) для планирования поста (проверяя историю запланированных постов)?2022-07-13 02:00:00
2022-07-13 05:00:00
2022-07-13 07:00:00
2022-07-13 08:00:00
2022-07-14 02:00:00
2022-07-14 05:00:00
2022-07-14 07:00:00
08:00
далее нужно опять переносить на следующий день и так по кругу.select ifnull(
(select
date_format(postdate, '%Y-%m-%d') as date
from autoposting_planned
where postdate > now()
group by date_format(postdate, '%Y-%m-%d')
having count(distinct find_in_set(date_format(postdate, '%H:%i'), '02:00,05:00,07:00,08:00')) < 4
order by date limit 1),
(select max(DATE_ADD(postdate, INTERVAL 1 DAY)) from autoposting_planned)
);
WITH
`slots` (`slot`) AS (
WITH RECURSIVE
`dates` (`date`, `add`) AS (
SELECT CURDATE(), 1
UNION SELECT `date` + INTERVAL 1 DAY, `add` + 1
FROM `dates`
WHERE `add` < 365
),
`hours` (`hour`, `idx`) AS (
WITH
`c` (`count`) AS (
SELECT LENGTH('02:00,05:00,07:00,08:00') - LENGTH(REPLACE('02:00,05:00,07:00,08:00', ',', '')) + 1
)
SELECT SUBSTRING_INDEX('02:00,05:00,07:00,08:00', ',', 1), 1
UNION SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('02:00,05:00,07:00,08:00', ',', `idx` + 1), ',', -1), `idx` + 1 AS `idx`
FROM `hours`
JOIN `c`
WHERE `idx` < `c`.`count`
)
SELECT CAST(CONCAT(`date`, ' ', `hour`) AS DATETIME) AS `slot`
FROM `dates`
JOIN `hours`
HAVING `slot` > CURTIME()
)
SELECT `s`.`slot`
FROM `slots` AS `s`
LEFT JOIN `autoposting_planned` AS `ap`
ON `ap`.`datetime` = `s`.`slot`
WHERE `ap`.`datetime` IS NULL
ORDER BY `s`.`slot`
LIMIT 1