仅选择字段中具有未来日期的记录 - SELECT only records with future date in field

我只想选择未来的事件

event    s_date    start_time  end_time
event6   2015-01-30  19:45:00  21:45:00
event5   2015-01-29  20:45:00  21:45:00
event4   2015-01-29  19:45:00  21:45:00
event3   2015-01-28  00:30:00  03:00:00
event2   2015-01-27  09:00:00  11:00:00
event1   2015-01-26  19:45:00  21:45:00

我试过这个，但没有得到正确的结果

SELECT s_date, DATE_FORMAT(s_date, '%W %D %M') AS date, CONCAT(s_date, ' ', end_time) AS dt
FROM schedule
GROUP BY s_date
HAVING dt >= NOW()

您可以使用

TIMESTAMP()连接日期和时间，然后将其与当前时间进行比较。

例如，要获取尚未开始的事件列表，请执行以下操作：

SELECT 
  event, start_time, end_time
FROM
  events
WHERE
  TIMESTAMP(s_date, start_time) > NOW()
;

请注意，这假定start_time和end_time属于 TIME 类型。应考虑完全删除s_date字段，并将start_time和end_time存储为DATETIME字段。

切记不要为此使用TIMESTAMP字段。尽管它们看起来相似，但每当记录更改时TIMESTAMP都会更新，而DATETIME只存储您显式为其提供的值。

我不太确定你想用你的GROUP BY条款做什么。如果您使用它，它只会为每个日期提供一个事件。它甚至不会为您提供该日期的第一个事件，因为您没有明确设置订单。

如果你能澄清你对这部分的意图，我很乐意尝试改进这个答案。

您正在使用group by，但您没有聚合任何列...因此，在评估"concat（）"表达式时获得的值将是随机的。

检查此SQL小提琴以获取解决方案：

SQL 小提琴

MySQL 5.5.32 架构设置：

create table schedule(
  event varchar(50),
  s_date date,
  start_time time,
  end_time time
);
insert into schedule 
values ('event6', '2015-01-30', '19:45:00', '21:45:00')
     , ('event5', '2015-01-29', '20:45:00', '21:45:00')
     , ('event4', '2015-01-29', '19:45:00', '21:45:00')
     , ('event3', '2015-01-28', '00:30:00', '03:00:00')
     , ('event2', '2015-01-27', '09:00:00', '11:00:00')
     , ('event1', '2015-01-26', '19:45:00', '21:45:00');

查询 1：

select s.*
from schedule as s
where concat(s_date, ' ', end_time) > now()

结果：

|  EVENT |                         S_DATE |                     START_TIME |                       END_TIME |
|--------|--------------------------------|--------------------------------|--------------------------------|
| event6 | January, 30 2015 00:00:00+0000 | January, 01 1970 19:45:00+0000 | January, 01 1970 21:45:00+0000 |
| event5 | January, 29 2015 00:00:00+0000 | January, 01 1970 20:45:00+0000 | January, 01 1970 21:45:00+0000 |
| event4 | January, 29 2015 00:00:00+0000 | January, 01 1970 19:45:00+0000 | January, 01 1970 21:45:00+0000 |
| event3 | January, 28 2015 00:00:00+0000 | January, 01 1970 00:30:00+0000 | January, 01 1970 03:00:00+0000 |

查询 2：

-- Little trick: 
-- In MySQL you can use HAVING without GROUP BY
-- to filter data using column aliases
select s.*, concat(s_date, ' ', end_time) as e_datetime
from schedule as s
having e_datetime > now()

结果：

|  EVENT |                         S_DATE |                     START_TIME |                       END_TIME |          E_DATETIME |
|--------|--------------------------------|--------------------------------|--------------------------------|---------------------|
| event6 | January, 30 2015 00:00:00+0000 | January, 01 1970 19:45:00+0000 | January, 01 1970 21:45:00+0000 | 2015-01-30 21:45:00 |
| event5 | January, 29 2015 00:00:00+0000 | January, 01 1970 20:45:00+0000 | January, 01 1970 21:45:00+0000 | 2015-01-29 21:45:00 |
| event4 | January, 29 2015 00:00:00+0000 | January, 01 1970 19:45:00+0000 | January, 01 1970 21:45:00+0000 | 2015-01-29 21:45:00 |
| event3 | January, 28 2015 00:00:00+0000 | January, 01 1970 00:30:00+0000 | January, 01 1970 03:00:00+0000 | 2015-01-28 03:00:00 |

希望这有帮助

我希望我能说对你：

select * from 
(select s_date, max(concat(s_date,' ',start_time)) as dt
from schedule 
group by s_date
) as x
where STR_TO_DATE(x.dt, '%Y-%c-%e %r') >= NOW()

我认为未来的事件是尚未播出的事件。