none
Ошибка в работе datediff

    Вопрос

  • Добрый день.

    Столкнулся с ситуацией, что datediff при расчетах теряет миллисекунду. Далее упрощенный блок моего кода. Если вызвать datediff(ms, '20181011 11:41:00.000', '20181011 11:41:01.927'), то в результате 1927. Но при использовании наборов данных получается 1926

    --Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) 
    --	Aug 19 2014 12:21:34 
    --	Copyright (c) Microsoft Corporation
    --	Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600:
    
    create database temp
    go
    
    use temp
    go
    
    if OBJECT_ID('tempdb..#buffer_table ','U') is not null 
    begin
    	drop table #buffer_table 
    end
    create table #buffer_table(
    	[dt] [datetime] NOT NULL,
    	[id] [int] NOT NULL,
    	[val] [float] NULL,
    	[error] [smallint] NULL
    )
    
    insert into 
    	#buffer_table (dt, id, val, error)
    select 
    	convert(datetime, o.dt,  108) dt, 
    	o.id, 
    	o.val, 
    	o.error 
    from
    	(
    		select '20181011 11:43:08.443',	40077,	61.84896,	0
    		union all
    		select '20181011 11:41:01.927',	40077,	61.84896,	100
    		union all
    		select '20181011 11:40:55.453',	40077,	61.84896,	0
    	) o (dt, id, val, error)
    
    
    if OBJECT_ID('tempdb..#range_table', 'U') is not null 
    begin
    	drop table #range_table
    end
    select 
    	b.id, 
    	coalesce(
    		null,
    		dateadd(mi, datediff(mi, 0, min(b.dt)), 0)) dt_min_h, 
    	min(b.dt) dt_min, 
    	max(dt) dt_max
    into 
    	#range_table
    from 
    	#buffer_table  b
    group by b.id;
    
    select * from #buffer_table
    select * from #range_table
    
    declare @dt1 datetime		
    declare @dt2 datetime		
    declare @dt_max datetime	
    declare @count int			
    
    select @dt1 = MIN(dt_min_h), @dt_max = dateadd(mi, datediff(mi, 0, max(dt_max)), 0) from #range_table  
    set @dt2 = dateadd(mi, 1, @dt1)
    
    while (@dt1<@dt_max)
    begin
    	if (datediff(mi, @dt1, '20181011 11:41:00.000') = 0)
    	begin  
    	with min_val as
    	(select 
    		(select max(b2.dt) from #buffer_table  b2 where b2.id = b1.id and b2.dt <= @dt1) dt_min, 
    		b1.id 
    	from 
    		#range_table  r
    	inner join 
    		#buffer_table  b1 on r.id = b1.id
    	where 
    		b1.dt > @dt2 and r.dt_min_h <= @dt1 
    	group by
    		b1.id)
    
    	select 
    		b.id,
    		(select MAX(t1.dt) from (values (b.dt), (@dt1)) t1(dt)),
    		(select 
    			min(t2.dt) 
    		from 
    			(select min(b3.dt) from #buffer_table  b3 where b3.id = b.id and b3.dt > b.dt 
    			union all 
    			select @dt2) t2(dt)),
    		datediff(
    			ms, 
    			@dt1,
    			(select 
    				min(t2.dt) 
    			from 
    				(select min(b3.dt) from #buffer_table  b3 where b3.id = b.id and b3.dt > b.dt 
    				union all 
    				select @dt2) t2(dt))), 
    		datediff(ms, '20181011 11:41:00.000', '20181011 11:41:01.927'),
    		b.error
    	from 
    		#buffer_table  b
    	inner join 
    		min_val m on m.id = b.id
    	where 
    		b.dt >= m.dt_min
    		and b.dt < @dt2;
    	end
    	set @dt1 = @dt2
    	set @dt2 = dateadd(mi, 1, @dt1)
    end
    
    --40077	2018-10-11 11:41:01.927	2018-10-11 11:42:00.000	60000	1927	100
    --40077	2018-10-11 11:41:00.000	2018-10-11 11:41:01.927	1926	1927	0
    С чем связанно такое поведение datediff?


    • Изменено northwind69 11 октября 2018 г. 8:12
    11 октября 2018 г. 8:10

Ответы

  • Разобрался. По умолчанию MS SQL 2008 использует тип datetime2. Тип datetime не обладает точностью до миллисекунд "Rounded to increments of .000, .003, or .007 seconds"

    По этому следующий код вернет два разных результата:

    declare @dt1 datetime		
    declare @dt2 datetime	
    set	@dt1 = '20181011 11:41:00.000' 
    set	@dt2 = '20181011 11:41:01.927'
    	
    select 
    	DATEDIFF(ms, convert(datetime, @dt1,  108), convert(datetime, @dt2,  108)),
    	DATEDIFF(ms, convert(datetime2, @dt1,  108), convert(datetime2, @dt2,  108))

    • Помечено в качестве ответа northwind69 12 октября 2018 г. 1:10
    12 октября 2018 г. 1:10

Все ответы

  • Упростил запрос. Проблема в convert. Без него считает правильно. Кто-нибудь может пояснить почему?

    create database temp
    go
    
    use temp
    go
    
    if OBJECT_ID('tempdb..#buffer_table ','U') is not null 
    begin
    	drop table #buffer_table 
    end
    
    select 
    	o.dt,--convert(datetime, o.dt,  108) dt, 
    	o.id, 
    	o.val, 
    	o.error 
    into 
    	#buffer_table
    from
    	(
    		select '20181011 11:43:08.443',	40077,	61.84896,	0
    		union all
    		select '20181011 11:41:01.927',	40077,	61.84896,	100
    		union all
    		select '20181011 11:40:55.453',	40077,	61.84896,	0
    	) o (dt, id, val, error)
    
    declare @dt1 datetime		
    set @dt1 = '20181011 11:41:00.000' 
    
    select
    	DATEDIFF(ms, @dt1, dt),
    	DATEDIFF(ms, '20181011 11:41:00.000', dt)
    from 
    	#buffer_table

    Но convert добавлял, так как во временную таблицу почему-то писалось время в формате с усечением секунд.

    11 октября 2018 г. 9:44
  • Разобрался. По умолчанию MS SQL 2008 использует тип datetime2. Тип datetime не обладает точностью до миллисекунд "Rounded to increments of .000, .003, or .007 seconds"

    По этому следующий код вернет два разных результата:

    declare @dt1 datetime		
    declare @dt2 datetime	
    set	@dt1 = '20181011 11:41:00.000' 
    set	@dt2 = '20181011 11:41:01.927'
    	
    select 
    	DATEDIFF(ms, convert(datetime, @dt1,  108), convert(datetime, @dt2,  108)),
    	DATEDIFF(ms, convert(datetime2, @dt1,  108), convert(datetime2, @dt2,  108))

    • Помечено в качестве ответа northwind69 12 октября 2018 г. 1:10
    12 октября 2018 г. 1:10