如何获取一天中两个不同时间之间的记录，而另一天的数据在同一张表中

Question

如何获取一天中两个不同时间之间的记录，而另一天的数据在同一个表中。

表：

id reported_date         dept
1  2017-11-23 09:00:05   IT
2  2017-11-22 21:00:10   IT
3  2017-11-23 10:00:09   SC
4  2017-11-22 22:00:20   SC
5  2017-11-23 05:00:30   IT

上述数据是基于以下标准来获取的:晚上8:00 (昨天)-晚上8:00 (今天)-将在今天的表格中。

现在我想要标记数据，如果它是在晚上8:00 (前一天)到早上6:00(今天)之间的“延迟”。作为新列或temp变量。

在上表中，带有2,4,5的记录将被标记为“late”，但您不应手动将时间范围设置为8-6。最小时间记录在晚上8:00之后，最大时间记录在早上6:00之前请帮帮我。

Answer 1

SELECT *
FROM tablename
WHERE hour(reported_date)<6 or
      hour(reported_date)>20

Answer 2

用mysql修改和编写的答案

/*SET @TODAY_TIMESTAMP = CURRENT_TIMESTAMP;*/ /*use this for live code*/
SET @TODAY_TIMESTAMP = '2017-11-23 14:27:10'; /*just for test scenary date of question was posed on */
SET @YesterdayEightPM = concat_ws(' ', DATE( DATE_ADD(@TODAY_TIMESTAMP, interval -1 day)),  ' 20:00:00'); 
SET @TodaySixAm  = concat_ws(' ', DATE(@TODAY_TIMESTAMP), '06:00:00');

CREATE temporary table  IF NOT EXISTS TBL_TEMP
( 
    id int,
    reported_date DATETIME,    
    dept varchar(40)
);

DELETE FROM TBL_TEMP where 1=1;

INSERT INTO TBL_TEMP (id,reported_date,dept)
VALUES (1,'2017-11-23 09:00:05', 'IT'),
       (2,'2017-11-22 21:00:10','IT'),
       (3,'2017-11-23 10:00:09','SC'),
       (4,'2017-11-22 22:00:20','SC'),
       (5,'2017-11-23 05:00:30','IT');   

SELECT ID, reported_date, dept,
       CASE WHEN reported_date BETWEEN CAST(@YesterdayEightPM AS DATETIME) AND CAST(@TodaySixAm AS DATETIME) THEN 'Late'                                       
       ELSE  'Normal' END AS 'Type',
       @TODAY_TIMESTAMP As TODAY_TIMESTAMP, 
       @YesterdayEightPM AS Yesterday8PM, 
       @TodaySixAm AS Today6AM
FROM TBL_TEMP;

用sql server编写的原始答案

DECLARE @TODAY_TIMESTAMP DATE = CURRENT_TIMESTAMP
DECLARE @YesterdayEightPM DATETIME = CAST(DATEADD(DD, -1,@TODAY_TIMESTAMP) as varchar(50)) + ' 20:00:00.000'
DECLARE @TodaySixAm DATETIME = CAST(@TODAY_TIMESTAMP as varchar(50)) + ' 06:00:00.000'

SELECT @YesterdayEightPM AS Yesterday8PM, @TodaySixAm AS Today6AM

DECLARE @TBL_TEMP TABLE 
( 
    [id] [int] NOT NULL,
    [reported_date] [DATETIME] NOT NULL,    
    [dept] [varchar](40) NULL   
)

INSERT INTO @TBL_TEMP (ID, reported_date, dept)
VALUES  (1,'2017-11-23 09:00:05', 'IT'),
        (2,'2017-11-22 21:00:10','IT'),
        (3,'2017-11-23 10:00:09','SC'),
        (4,'2017-11-22 22:00:20','SC'),
        (5,'2017-11-23 05:00:30','IT')

SELECT  ID, reported_date, dept,
        CASE WHEN TT.reported_date BETWEEN     @YesterdayEightPM AND @TodaySixAm THEN 'Late'
        ELSE 'Normal'END
FROM @TBL_TEMP TT   WHERE 1=1