php - MySQL - 为每个日期选择具有隔夜时间的日期范围

Question

我有一个包含日期时间的表，选择时我想指定日期范围和时间范围(过夜算作前一天)。

例子:

CREATE TABLE some_table (
id integer NOT NULL,
date datetime NOT NULL,
status integer NOT NULL,
x integer NOT NULL
);

insert into some_table(id, date, status, x)
values
(1, '2017-11-16 11:05:00', 1, 15),
(2, '2017-11-16 14:33:00', 1, 2),
(3, '2017-11-16 18:02:00', 1, 57),
(4, '2017-11-16 18:29:00', 1, 41),
(5, '2017-11-16 22:05:00', 1, 3),
(6, '2017-11-17 01:47:00', 1, 105),
(8, '2017-11-17 03:55:00', 1, 41),
(9, '2017-11-17 07:07:00', 1, 77),
(10, '2017-11-17 11:25:00', 1, 52),
(11, '2017-11-17 18:12:00', 1, 1),
(12, '2017-11-17 20:51:00', 1, 58)

目前我使用“间隔”来表示从 (YYYY-mm-dd 03:50:00) 到 (YYYY-mm-dd 03:50:00)* 第二天的时间范围。

但它没有按预期工作!

select  
  sum(x) AS total_x , 
  str_to_date(date + INTERVAL '03:50' HOUR_MINUTE,'%Y-%m-%d') datex
from    some_table
WHERE
date between '2017-11-14' and '2017-11-18'
group by datex

以上结果为:

 total_x  |    datex
 ------------------------
 115      |   2017-11-16
 279      |   2017-11-17
 58       |   2017-11-18

我想实现的目标:

 total_x  |    datex
 ------------------------
 223      |   2017-11-16
 229      |   2017-11-17

找到上面的代码:http://sqlfiddle.com/#!9/e320f9/37

Answer 1

   select sum(x),aa.date from (
     select date(subtime(date,'0 3:50:0.0')) as date,x from some_table) aa 
     group by aa.date