优雅的方式来计算最近的时间
[英]Elegant way to calculate the nearest time
I have a reference time:
我有一个参考时间:
'2012-05-01 23:35:00'
And I have a couple of more times from some table where the 'yyyy-MM-dd' date part is irrelevant but what is relevant is date part with 'HH:minute'
而且我在某些表格中有几次“yyyy-MM-dd”日期部分无关紧要,但相关的是日期部分与'HH:分钟'
------------------------------
| ID | Time |
| -- |-----------------------|
| 01 | '1900-01-01 13:10:00' |
| 02 | '1900-01-01 07:01:00' |
| 03 | '1900-01-02 00:45:00' |
| 04 | '1900-01-02 18:00:00' |
------------------------------
I am writing a function that will return the row with the nearest time (in above example this would be ID 03), but I don't like what I am writing.
我正在编写一个函数,它将返回最近时间的行(在上面的示例中,这将是ID 03),但我不喜欢我写的内容。
So I started to observe the problem graphically. Is there maybe a way to do this with Atan function?
所以我开始以图形方式观察问题。有没有办法用Atan功能做到这一点?
Edit: I am using MSSQL server 2005
编辑:我正在使用MSSQL server 2005
1 个解决方案
#1
3
declare @reftime datetime;
set @reftime = '20120501 23:00:00';
select TOP(1) with ties
id, time
from (
select
t.*,
dateadd(d, -datediff(d, 0, @reftime), @reftime) reftime,
dateadd(d, -datediff(d, 0, t.time), t.time) coltime
from tbl t
) x
order by (select min(diff)
from
(select abs(datediff(ms, reftime, coltime))
union all
select abs(datediff(ms, reftime+1, coltime))
union all
select abs(datediff(ms, reftime, coltime+1))) y(diff));
Notes:
- The
datediff(d, -datediff(...patterns remove the date portion from the datetimes. - To handle cross-midnight scenarios, both
reftime+1andcoltime+1are tested in addition to justabs(reftime-coltime). - The
ORDER BYis performed across the minimum difference from testing it over all 3 scenarios.
dateiff(d,-datediff(...模式从日期时间中删除日期部分。
为了处理跨午夜场景,除了abs(reftime-coltime)之外,还测试了reftime + 1和coltime + 1。
ORDER BY是在所有3个场景中测试它的最小差异。
注意！
本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2012/11/16/b9bc4bc71956bed3120d3be9ccea4ad0.html。