php - SQL获取给定日期下一次出现的日期戳

我有以下SQL查询：

SELECT
  *
FROM
  tbl_clubs c
  LEFT JOIN tbh_club_classes cc
    ON c.clubID = cc.clubID
  LEFT JOIN tbl_classes cl
    ON cl.classID = cc.classID

这样返回数据

Array
(
[0] => Array
    (
        [clubID] =>
        [clubName] => Club 1
        [clubLocation] => Clyme Hall
        [clubPostcode] =>
        [classID] => 2
        [classType] => Kickboxing
        [classStart] => 17:00
        [classEnd] => 19:00
        [classNight] => Monday
        [classAudience] => Mixed
        [classCost] => 5.00
    )

[1] => Array
    (
        [clubID] => 1
        [clubName] => club 2
        [clubLocation] => Sports Hall
        [clubPostcode] =>
        [classID] => 3
        [classType] => Karate / Judo
        [classStart] => 19:30:00
        [classEnd] => 21:00:00
        [classNight] => Friday
        [classAudience] => Mixed
        [classCost] => 5.00
    )
)

我希望classNight返回存储在classNight中的星期几，某月的某天以及该日期的下一次出现的月份，以便从查询中获得类似的信息。

Array
(
[0] => Array
    (
        [clubID] =>
        [clubName] => Club 1
        [clubLocation] => Clyme Hall
        [clubPostcode] =>
        [classID] => 2
        [classType] => Kickboxing
        [classStart] => 17:00
        [classEnd] => 19:00
        [classNight] => **Mon 26th Oct**
        [classAudience] => Mixed
        [classCost] => 5.00
    )

[1] => Array
    (
        [clubID] => 1
        [clubName] => club 2
        [clubLocation] => Sports Hall
        [clubPostcode] =>
        [classID] => 3
        [classType] => Karate / Judo
        [classStart] => 19:30:00
        [classEnd] => 21:00:00
        [classNight] => **Fri 21st Oct**
        [classAudience] => Mixed
        [classCost] => 5.00
    )
)

我想修改我的查询以获取上面想要的结果。因此，正在发生的是classNight字段，它是一种varcar格式，用于保存该课程所在的星期几，我希望它返回存储在classNight中的星期几，每月的某天以及第二天的第二个月。 。

我已经使用php编写了此代码

$day = $row['classNight'];//ie Monday, Tuesday etc
$today = strtotime('today 00:00:00');
$date = strtotime($day."+1",$today);
$date1 = date("D d M",$date);

但我需要弄清楚如何在SQL查询中执行此操作。

编辑

也许不是查询返回星期几/星期几，而是查询返回存储在classNight字段中的下一个实例的日期戳，然后我可以使用php以我需要的格式显示它，这对每个人来说都比较容易:)

我找到了此代码，但无法正常工作

SELECT * (NEXT_DAY(SYSDATE,`classNight`) "NEXT DAY") AS dates
 FROM tbl_classes;

我不确定这会给我时间戳，但是应该给我日/月/年。谁能看到上面的问题以及如何将其合并到查询中？

最佳答案

如果我理解正确，那么您想查找一周中某天的第二天：

SELECT *,
       (curdate + interval (case when field(dow, 'Sunday', . . . , 'Sunday') >= dayofweek(curdate)
                                 then field(dow, 'Sunday', . . . , 'Sunday') - dayofweek(curdate)
                                 else 7 + field(dow, 'Sunday', . . . , 'Sunday') - dayofweek(curdate)
                            end) day
        ) as next_class_day
FROM tbl_clubs c LEFT JOIN
     tbh_club_classes cc
     ON c.clubID = cc.clubID LEFT JOIN
     tbl_classes cl
     ON cl.classID = cc.classID;