mysql查询以查找星期几内开始/结束时间之间的记录

oogrdqng 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(380)

我们是一个广播电台，节目有开始和结束时间，在一周的特定日子，在一年的特定星期。我们有从周六到周日（周六晚上11点到周日凌晨1点）的夜间演出，也有安排在周一、周二、周三的演出。
我们正在尝试创建一个对mysql的查询，查询某个时间正在播出的节目。我们目前的调查不可能做到这一点。
如何构造对当前表的一个或多个查询，只提供时间、星期几、一年中的星期。表格是否需要修改以包含更多信息？谢谢您。
我们尝试了下面的表构造和mysql查询。

mysql数据库：

CREATE TABLE `shows` (
    `id`        tinyint(4) NOT NULL,
    `name`      varchar(255) NOT NULL COMMENT 'Program Name',
    `starts`    time NOT NULL COMMENT 'Starting Time',
    `ends`      time NOT NULL COMMENT 'Ending Time',
    `duration`  smallint(6) NOT NULL COMMENT 'Length in seconds',
    `days` set('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT NULL COMMENT 'Starting Day of the Week',
    `weeks`     set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53') DEFAULT NULL COMMENT 'Weeks of the Year',
    `active`     tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Active Program'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

PHP：

$time = date("H:i:s"); // current time
$dow = date("l"); // current Day of Week
$week = date("W");// current week

$query = "SELECT id, name, starts, ends, duration, days, weeks, active
          FROM shows WHERE starts < $time AND ends > $time
          AND FIND_IN_SET('$dow', days)
          AND FIND_IN_SET('$week', weeks)
          AND active = '1'";

**示例数据：**节目开始于周六晚上11点，结束于周日凌晨1点

{
    "id": "123",
    "name": "Overnight Show",
    "starts": "23:00:00",
    "ends": "01:00:00",
    "duration": "7200",
    "days": "Saturday",
    "weeks": "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53",
    "active": "1"
}

对于给定的输入$time=00:12:00，$dow=sunday，$weeks=52，返回一个空的结果集，因为节目在周六开始。

mysql DateTime php

来源：https://stackoverflow.com/questions/53981501/mysql-query-to-find-record-between-start-end-time-within-day-of-week

2条答案

按热度按时间

kuhbmx9i1#

要保持当前架构，可以通过以下方式更改查询：

$time = date("H:i:s"); // current time
$dow = date("l"); // current day of Week
$dow_ystd = date("l", time() - 60*60*24); // day of Week of yesterday
$week = date("W");// current week

$query = "SELECT id, name, starts, ends, duration, days, weeks, active
          FROM shows WHERE

          (
            ( (ends > starts) AND (FIND_IN_SET('$dow', days)) AND (starts <= '$time') AND (ends >= '$time') )
            OR
            ( (ends <= starts) AND (FIND_IN_SET('$dow_ystd', days)) AND (ends > '$time') )
          )
        AND FIND_IN_SET('$week', weeks)
        AND active = '1'";

请记住，这不适用于超过24小时（即跨越多天）的节目。为了支持这种情况，您必须更深入地更改模式。

赞(0）回复(0）举报 2021-06-18

yc0p9oo02#

您的表（至少）有两个问题：
既有开始时间也有结束时间，但只有开始日，这意味着你的节目在同一天内开始和结束（在这个例子中，从星期六23点到星期六1点——在过去，或者在一整周内）。
使用字符串列表作为值不仅效率低下（如果没有太多行，这是可以原谅的），而且还会带来问题，例如查找第二周的 %2% 将匹配 2 , 12 , 22 等等。解决方法是添加逗号 , 在值的开头和其他值的结尾，并搜索 %,2,% （您必须添加 , ，在数据中或在查询时使用 concat() ，因为否则它将不匹配第一个或最后一个值）。
实际的解决方案是添加两个表：
您当前的 shows ，但没有 starts , ends , days 以及
weeks shows_schedule 带有开始时间和持续时间（不是结束时间） shows_schedule_weeks 每周都有一排
给我点时间，我再举个例子。

赞(0）回复(0）举报 2021-06-17

我来回答

mysql查询以查找星期几内开始/结束时间之间的记录

2条答案

相关问题

热门标签

最新问答