如何在sql中只比较和使用来自datetime格式的时间？我的插入是以日期时间格式给出的

Question

请注意，这不是我查询的完整代码。我只需要了解如何比较时间。下面是我的表创建代码

CREATE TABLE IF NOT EXISTS `mydb`.`ActivityBooking` (
  `ActivityTime` datetime  NOT NULL,
  `NumPeople` INT NULL,
  `ActivityID` VARCHAR(45) NOT NULL,
  `GuideID` VARCHAR(10) NOT NULL,
  `Reservation_ReservationID` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`ActivityTime`, `ActivityID`),
  INDEX `fk_ActivityBooking_Activity1_idx` (`ActivityID` ASC) VISIBLE,
  INDEX `fk_ActivityBooking_Staff1_idx` (`GuideID` ASC) VISIBLE,
  INDEX `fk_ActivityBooking_Reservation1_idx` (`Reservation_ReservationID` ASC) VISIBLE,
  CONSTRAINT `fk_ActivityBooking_Activity1`
    FOREIGN KEY (`ActivityID`)
    REFERENCES `mydb`.`Activity` (`ActivityID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ActivityBooking_Staff1`
    FOREIGN KEY (`GuideID`)
    REFERENCES `mydb`.`Staff` (`StaffID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ActivityBooking_Reservation1`
    FOREIGN KEY (`Reservation_ReservationID`)
    REFERENCES `mydb`.`Reservation` (`ReservationID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

表格插入

Insert into activitybooking values('2020-10-7 15:17:00',6,'C01','S5','R321');
Insert into activitybooking values('2020-12-8 16:15:00',7,'D01','S2','R321');
Insert into activitybooking values('2020-11-9 18:12:00',2,'E01','R321','S4');

目前我卡住了，因为我想要显示中午之后和下午4点之前预订的结果但我不知道如何从datetime格式比较时间。我不会添加其他表的代码，以便将此处的信息保持在最少。但如果需要，我可以提供。

select customerfname, customerlname, activityname
from customer, activitybooking,activity
where activity.activityid = activitybooking.activityid
and activitytime between time('12-0-0') and time('16-0-0');

Answer 1

您需要做的是将TIME函数应用于activitytime列，然后将其与12:00:00和16:00:00进行比较

TIME(activitytime) BETWEEN '12:00:00' AND '16:00:00'

注如果您不想包含16:00:00，请用15:59:59替换它。此外，如果您以HH:mm:ss格式编写时间字符串，则不需要对其应用TIME，只需直接比较即可。