Как я могу объяснить отсутствие точности даты и времени в миллисекундах при выполнении сравнений даты и времени?
Фон
Недавно я узнал, что тип datetime в SQL Server хранит время примерно с точностью до 1/300 секунды.
Итак, учитывая эти данные:
create table datetime_test (id int, my_date datetime);
insert into datetime_test (id, my_date) values
(0, '2020-12-11 17:14:07.000'),
(1, '2020-12-11 17:14:07.197'),
(2, '2020-12-11 17:14:07.198'),
(3, '2020-12-11 17:14:08.000');
Этот запрос вернет (1, 2, 3) вместо (2, 3), как можно было бы ожидать:
select id from datetime_test where my_date >= '2020-12-11 17:14:07.198';
Причина в том, что миллисекундная часть этой даты и времени фактически хранится как .197:
-- Result: 2020-12-11 17:14:07.197
select convert(datetime, '2020-12-11 17:14:07.198');
Мой вопрос
Я работаю с существующим кодом С#, который использует SQL для сравнения даты и времени с помощью >=. Что-то вроде этого:
public Foo GetMyColumn(DateTime inputDatetime)
{
// ...
}
select my_column
from my_table
where my_datetime >= @inputDatetime
Я пытаюсь повторно использовать этот метод С# для выполнения эксклюзивного сравнения... эквивалент использования >. Как мне это сделать?
Моя первоначальная попытка состояла в том, чтобы добавить одну миллисекунду к вводу даты и времени (в С#), но это не сработает из-за проблемы с точностью, описанной выше. Я полагаю, что мог бы добавить 3 миллисекунды. Это похоже на взлом. Но будет ли он работать надежно?
Примечание: предположим, что я не могу изменить этот SQL или метод.
@GordonLinoff согласился. Но в этом случае я работаю с существующей кодовой базой/базой данных, которую очень сложно обновлять. Поэтому мне интересно, есть ли обходной путь.
. . Я думаю, что ваш хак должен добавить 4 миллисекунды, потому что иногда добавление 3 просто приводит к тому же значению. Но я подозреваю, что вы сможете выполнять часть работы в базе данных, а не в коде C#, что может быть самым безопасным методом.
Рассмотрим строку 2 — единственный способ получить ее — использовать запрос >= 197 или аналогичный. То же самое преобразование, которое происходит с вашей датой запроса, происходит при сохранении дат. Невозможно изменить только этот запрос, чтобы получить только строки 2 и 3 на основе сравнения на основе даты и времени. Или, другими словами, .197 и .198 неразличимы в базе данных.
@Damien_The_Unbeliever - извините, я вижу, как моя справочная информация может сбивать с толку. Я хочу запрашивать данные в том виде, в каком они существуют в базе данных, а не в том виде, в каком я написал оператор insert. Так что в этом случае ответ, который я бы искал, просто (3).
Я вижу здесь, что datetime округляет миллисекунды до .xx0, .xx3 или .xx7. Итак, предполагая, что это правда, я полагаю, что могу округлить ввод до следующего числа, следуя этому шаблону. Конечно, это большой хак, но я просто изучаю свои варианты.
Ответы 2
Вы обнаружите, что часы Windows могут быть не такими точными, как вам может понадобиться. Кроме того, часы вашего сервера не очень точны, в зависимости от нагрузки они могут отставать, по этой причине мы используем специальное оборудование с GPS-таймером, когда требуется точное время.
Кроме того, время на вашем сервере и время на сервере БД не обязательно должны совпадать, если ваши требования жесткие, они не будут/могут «никогда не совпадать». Только сломанные часы точны 2 раза в день...
Небольшое примечание: всегда используйте дату и время в формате UTC в базе данных и в коде, чтобы у вас было одинаковое время независимо от того, где размещены серверы. Дата-время UTC имеет ToLocalTime() в .net, а база данных имеет GetUtcDate() в TSQL, поэтому вы не отключаетесь от часовых поясов.
using System;
using System.Runtime.InteropServices;
public static class HighResolutionDateTime
{
public static bool IsAvailable { get; private set; }
[DllImport("Kernel32.dll", CallingConvention = CallingConvention.Winapi)]
private static extern void GetSystemTimePreciseAsFileTime(out long filetime);
public static DateTime UtcNow
{
get {
if (!IsAvailable)
{
throw new InvalidOperationException("High resolution clock isn't available.");
}
long filetime;
GetSystemTimePreciseAsFileTime(out filetime);
return DateTime.FromFileTimeUtc(filetime);
}
}
static HighResolutionDateTime()
{
try
{
long filetime;
GetSystemTimePreciseAsFileTime(out filetime);
IsAvailable = true;
}
catch (EntryPointNotFoundException)
{ // Not running Windows 8 or higher.
IsAvailable = false;
}
}
}
#редактировать согласно комментарию что касается сравнения даты и времени, используйте datetime2 (7) в качестве типа данных в столбце данных, это будет так же точно, как .net datetime
Исходный ввод даты поступает из отдельного запроса к базе данных, поэтому я не думаю, что мне нужно беспокоиться о том, что часы не синхронизированы.
Тоже простите, а здесь есть ответ на мой вопрос? Я вижу кучу интересных побочных заметок, но ничего, что напрямую касается моего вопроса.
поэтому сохраните свои данные в поле DateTime2 (7) в базе данных, и вы будете округлять до часов SQL-серверов.
Согласно документации, datetime «округляется до 0,000, 0,003 или 0,007 секунды». Таким образом, обходной путь С# состоит в том, чтобы округлить ввод DateTime до следующего числа, следуя этому шаблону.
Что-то вроде этого:
private DateTime RoundUp(DateTime datetime)
{
int lastMillisecondDigit;
do
{
datetime = datetime.AddMilliseconds(1);
lastMillisecondDigit = datetime.Millisecond % 10;
} while (lastMillisecondDigit != 0 && lastMillisecondDigit != 3 && lastMillisecondDigit != 7);
return datetime;
}
Примечание. Я понимаю, что это большой взлом. В комментариях есть лучшие решения, такие как изменение типа данных на datetime2. Но если вы не можете изменить базу данных или код, я думаю, этот обходной путь поможет.
Я дал ему метод точного времени, ему это не понравилось
@WalterVehoeven Я ценю ваш вклад, но при всем уважении вы не ответили на мой вопрос. Мой вопрос заключался в том, как реализовать обходной путь с существующим кодом/базой данных. Я согласен, что datetime2 — лучший вариант, если вы можете изменить базу данных (как я отметил в этом ответе), но это был не мой вопрос.
Затем вам придется измерить разницу как меньшую, чем разница во времени DbTime-localtime> Timespan. у вас то же самое при сравнении двойников
Это. хорошая причина для использования
datetime2, который имеет гораздо больший контроль над точностью (Learn.Microsoft.com/en-us/sql/t-sql/data-types/…).