过滤时间值，它是指定时间任一侧的设定时间段

Given a specified time value and an interval value:

给定指定的时间值和间隔值:

Specified Time: 13:25:00
Interval Value: 00:20:00

How can I filter the following table of values to return times that are the specified Interval either side of the Specified Time.

如何过滤下面的值表以返回指定时间任一侧的指定时间间隔的时间。

12:45:24
13:05:00
13:50:30
14:50:32
15:15:10

I want a function or query to check if '13:25:00' has '00:20:00' difference with any of the times in table.

我想要一个函数或查询来检查'13:25:00'与表中的任何时间是否有'00:20:00'的差异。

The output should return:

输出应该返回:

13:05:00

3 个解决方案

#1

Based on the information you have provided, I assume you want to get values from the list that are the specified period either side of your "special time".

根据您提供的信息,我假设您希望从列表中获取“特殊时间”两侧指定时间段内的值。

Here's one way to do it using DATEADD:

以下是使用DATEADD执行此操作的一种方法:

-- temp table for your sample data
CREATE TABLE #times ( val TIME )

INSERT  INTO #times
        ( val )
VALUES  ( '12:45:24' ),
        ( '13:05:00' ),
        ( '13:50:30' ),
        ( '14:50:32' ),
        ( '15:15:10' )

DECLARE @special_time TIME = '13:25:00'      
DECLARE @diff_value TIME = '00:20:00'

-- variable will hold the total number of seconds for your interval
DECLARE @diff_in_seconds INT

-- gets the total number of seconds of your interval -> @diff_value 
SELECT  @diff_in_seconds = DATEPART(SECOND, @diff_value) + 60
        * DATEPART(MINUTE, @diff_value) + 3600 * DATEPART(HOUR, @diff_value)

-- get the values that match the criteria
SELECT  *
FROM    #times
WHERE   val = DATEADD(SECOND, @diff_in_seconds, @special_time)
        OR val = DATEADD(SECOND, -( @diff_in_seconds ), @special_time)

DROP TABLE #times

Note that the WHERE clause filters the results by adding and subtracting the difference. The subtraction is achieved by making the @diff_in_seconds negative.

请注意,WHERE子句通过添加和减去差异来过滤结果。通过使@diff_in_seconds为负来实现减法。

#2

If we are understanding your question correctly, you want all the times that are bigger than 20 minutes from your given (special) time.

如果我们正确理解您的问题,您希望所有时间超过给定(特殊)时间的20分钟。

To achieve this, just do a select with a where clause that contains a clause looking like this: abs(datediff(minute, tableDate, @specialdate)) > 20

要实现这一点,只需使用包含如下子句的where子句进行选择:abs(datediff(minute,tableDate,@ specialdate))> 20

SQLFiddle sample and code example:

SQLFiddle示例和代码示例:

declare @specialDate datetime = '1900-01-01 13:25:00'

select *
  from SampleData
 where abs(datediff(minute, SomeDate, @specialDate)) > 20

Note that I set the dates of the Datetime columns to 1900-01-01 as an obscure reference, adjust according to your settings.

请注意,我将Datetime列的日期设置为1900-01-01作为模糊参考,根据您的设置进行调整。

You will need the ABS in the line to make sure that both variants of the resulting datediff are checked (It can either bring back 0, > 0 or < 0)

您将需要行中的ABS以确保检查结果日期值的两个变体(它可以带回0,> 0或<0)

References:
MSDN: DATEDIFF
MSDN: ABS

参考文献:MSDN:DATEDIFF MSDN:ABS

#3

Here is a solution:

这是一个解决方案:

create table t(t time);

insert into t
values
    ('12:45:24'),
    ('13:05:00'),
    ('13:50:30'),
    ('14:50:32'),
    ('15:15:10')

declare @st time = '13:25:00'
declare @dt time = '00:20:00'

select * from t
where abs(datediff(ss, t, @st)) - datediff(ss, '00:00:00', @dt) = 0

abs(datediff(ss, t, @st) will hold difference in seconds between times in table and special time. You compare this difference to difference between 00:00:00 and interval datediff(ss, '00:00:00', @dt)

abs(datediff(ss,t,@st)将保持表格和特殊时间之间的秒数差异。您可以将此差异与00:00:00和interval datediff(ss,'00:00:00')之间的差异进行比较, @dt)

Output:

t
13:05:00.0000000

Fiddle http://sqlfiddle.com/#!6/05df4/1

#1