获取特定时间范围内的数据 [英] Get data for specific time range

问题描述

我正在尝试选择每天从2018-09-24到2018-09-26的晚上7点到7点之间的记录.
示例:

I'm trying to select records between 7 pm to 7 am from 2018-09-24 until 2018-09-26 for each day.
example:

2018-09-24 _____ 7 pm至7 am____ 2018-09-25
2018-09-25 _____晚上7点至上午7点____ 2018-09-26
2018-09-26 _____晚上7点至上午7点____ 2018-09-27

2018-09-24_____7 pm to 7 am____ 2018-09-25
2018-09-25_____7 pm to 7 am____ 2018-09-26
2018-09-26_____7 pm to 7am____ 2018-09-27

通过使用此代码A:我从晚上7点(2018-09-24)到早上7点(2018-09-26)获取记录.

by using this code A: I get records from 7pm(2018-09-24) until 7am(2018-09-26).

If Trim(cbShift.Text) = "Morning" Then
startdt = (S_date.Text) & " 19:00:00.317"
enddt = (E_date.Text) & " 07:00:00.160" End If
SELECT FROM tb_test
WHERE  (DateCreate   BETWEEN convert(datetime,@startdt) AND convert(datetime,@enddt))

，并使用此代码B:

WHERE  DateCreate > convert(datetime,'2018-09-24') AND DateCreate <=
DATEADD(day,1,convert(datetime,'2018-09-26'))
AND (DATEPART(hh,DateCreate) >= 19 and DATEPART(hh,DateCreate) <= 24  )

or DateCreate > convert(datetime,'2018-09-24') AND DateCreate <=
DATEADD(day,1,convert(datetime,'2018-09-26'))
AND (DATEPART(hh,DateCreate) >= 0 and DATEPART(hh,DateCreate) <= 6  )

我知道

2018-09-24(00-> 7和19-> 24)，2018-09-25(00-> 7和19-> 24)和2018-09-26(00-> 7和19 -> 24)

2018-09-24 (00->7 and 19 ->24) , 2018-09-25 (00->7 and 19 ->24), and 2018-09-26 (00->7 and 19 ->24)

有没有办法像上面的示例那样获取数据. 谢谢您的帮助

Is there a way I can do to get a data exactly like my example above. Thanks for your help

推荐答案

我认为问题出在WHERE子句中的评估顺序.我总是在SQL中的OR运算符周围使用括号.以下内容适用于MySQL 5.6.

I think that the problem is with the order of evaluation in your WHERE clause. I always use brackets around OR operators in SQL. The following works on MySQL 5.6.

SELECT * 
FROM tb_test
WHERE  datecreate between '2018-09-24 19:00:00' and '2018-09-26 07:00:00'
AND (hour(DateCreate) >= 19 or hour(DateCreate) < 7);

作为参考，我使用了以下测试模式:

For reference, I used this test schema:

CREATE table tb_test
(datecreate datetime);

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
 SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
 SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
 SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
 SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
 SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
 FROM generator_16 lo, generator_16 hi;

INSERT INTO tb_test
SELECT date_add("2018-09-24",interval n hour)
FROM generator_256;

这篇关于获取特定时间范围内的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！