Implementando um esquema de partição rotativa, por kejser.org/table-pattern-rotating-log-ring- . Tive um problema com DATEDIFF arredondando valores:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, dtTrunc
, dtDiff
, PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
(
VALUES
('2024-08-17 23:59:59.997')
, ('2024-08-17 23:59:59.998')
, ('2024-08-17 23:59:59.999')
, ('2024-08-18 00:00:00.000')
)
AS v(dt)
CROSS APPLY
(
SELECT
dt = CAST(v.dt AS DATETIME2(3))
) a1
CROSS APPLY
(
SELECT
dtTrunc = CAST(a1.dt AS DATE)
, dtDiff = DATEDIFF(day, 0, a1.dt)
) a2
Problema resolvido com elenco até o momento:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, dtTrunc
, dtDiff
, PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
FROM
(
VALUES
('2024-08-17 23:59:59.997')
, ('2024-08-17 23:59:59.998')
, ('2024-08-17 23:59:59.999')
, ('2024-08-18 00:00:00.000')
)
AS v(dt)
CROSS APPLY
(
SELECT
dt = CAST(v.dt AS DATETIME2(3))
) a1
CROSS APPLY
(
SELECT
dtTrunc = CAST(a1.dt AS DATE)
, dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE))
) a2
Esse é um comportamento esperado/documentado? Se sim, onde?
Isso só é esperado se você estiver familiarizado com as regras, mas elas nunca foram totalmente documentadas .
Este é particularmente o caso para o legado
datetime
esmalldatetime
tipos, que têm uma série de peculiaridades mantidas para compatibilidade com versões anteriores. Os tipos mais modernos comodatetime2
não permitem estranhezas como adicionar números a datas ou converter implicitamente inteiros. Muitas das peculiaridades são o resultado de decisões questionáveis do passado.Se quiser evitar surpresas, seja sempre explícito sobre os tipos de dados.
A desleixo de tipo é abundante no código original. Strings não são tipos de data/hora, elas são strings.
CAST
não pode aceitar um parâmetro de estilo (ao contrário deCONVERT
), o que pode resultar em ambiguidade, resultados não determinísticos ou até mesmo erros de tempo de execução. UsarDATEDIFF
com uma mistura de tipos de dados é apenas pedir problemas.Exemplo
Vamos pegar o exemplo de Jonathan Fite porque é mais simples que o original:
Os itens na
VALUES
cláusula são strings. Eles podem parecer datas e horas para um humano, mas não são.As expressões na
SELECT
cláusula estão à mercê das regras misteriosas para conversão de tipo de dados executadas pelasDATEDIFF
implementações (sim, existem várias).1.
UsingZero = DATEDIFF(DAY, 0, v.dt)
Aqui chamamos
DATEDIFF
com um inteiro e umavarchar
string como parâmetros.O SQL Server converte tanto o zero quanto a
dt
string paradatetime
, como você pode ver no plano de execução:Union1004
é o rótulo dado àsvarchar
strings daVALUES
cláusula. O literal '1900-01-01 00:00:00.000' é o resultado dobrado constante da conversão de zero paradatetime
. Observe que o formato da string exibida édatetime
-specific (ela tem três decimais fracionários).A conversão implícita de suas strings para
datetime
é a parte importante para explicar sua preocupação com arredondamento:Observe o arredondamento devido à precisão limitada de
datetime
(1/300s).Não é o zero que causa o arredondamento, é a conversão implícita de suas strings para
datetime
.2.
ConvertToDateTime = DATEDIFF(DAY, CONVERT(datetime, 0), v.dt)
Agora fornecemos
DATEDIFF
umdatetime
valor e umavarchar
string.O SQL Server converte ambos para
datetimeoffset
, como você pode ver no plano de execução:O literal '1900-01-01 00:00:00.000 +00:00' é o resultado dobrado constante da conversão de zero para
datetimeoffset(3)
. O formato da string exibida tem um deslocamento de fuso horário e três casas decimais. Sua string é convertida implicitamente paradatetimeoffset(7)
.Nenhum arredondamento ocorre neste caso porque o zero mapeia para um valor de data exato e todas as strings são exatamente representáveis.
3.
MishMash = DATEDIFF(DAY, CONVERT(datetime, 0), CONVERT(datetime2(3), v.dt))
Por fim, fornecemos
DATEDIFF
umdatetime
e o resultado da conversão dasvarchar
strings paradatetime2(3)
(com um estilo padrão).O plano de execução mostra o cast explícito para
datetime
constant-folded paradatetimeoffset(3)
e exibido com os três decimais esperados e um deslocamento de fuso horário. Suasvarchar
strings são primeiro cast explicitamente paradatetime2(3)
conforme solicitado pelo código, então implicitamente convertidas paradatettimeoffset(3)
:Provavelmente não era isso que pretendiam.
Sendo explícito
Você não disse qual era o tipo de dado dos valores de origem, mas tendo em mente
DATEDIFF
os usosdatetimeoffset
internos para a maioria dos cálculos, podemos muito bem escolher:Resultados:
O plano de execução mostra:
Não é tão fácil de ver, mas os
[Union1004]
valores também têm o tipo correto:Você pode escolher
datetime2(3)
ou qualquer coisa para corresponder exatamente ao tipo de dado de origem, mas então haverá uma conversão implícita nodatetimeoffset
plano porque é isso queDATEDIFF
é usado internamente nesse caso.Conselho
Escolha um estilo determinístico apropriado para conversões de data/hora de strings, prefira
CONVERT
overCAST
(que não aceita um estilo) e seja explícito sobre seus tipos de dados se não gostar de resultados confusos e situações difíceis de depurar em geral.Fiz uma pergunta relevante aqui. O que 0 em DATEDIFF(MINUTE, 0, <Date>) realmente significa?
Também implementei o mesmo esquema de rotação de logs no meu ambiente de produção e adoraria vê-lo mais usado! (grande fã de Thomas Kejser, pena que o site dele só esteja disponível no arquivo da web).
O problema que você está enfrentando é devido a como o SQL lida com o 0 quando usado como partes de datediff, ele não converte para o tipo esperado. Se você fizer uma conversão explícita para datetime (não pode fazer datetime2, a propósito), você obtém o resultado esperado. Eu também sugeriria uma conversão explícita do tipo table para o tipo desejado, mas isso provavelmente não está impactando sua solução real.