При работе с языком запросов 1С программисты нередко сталкиваются со следующей ошибкой выполнения запроса: «Функция datediff вызвала переполнение. Слишком большое количество частей даты, разделяющих 2 экземпляра даты-времени».
Давайте проанализируем, почему возникает эта ошибка, разберем внутренние механизмы СУБД, которые приводят к сбою, и рассмотрим наиболее эффективные архитектурные решения этой проблемы на практических примерах.
Рассмотрим классический пример запроса, в котором разработчик пытается ограничить выборку товаров по сроку годности, вычисляя разницу в секундах:
ВЫБОР
КОГДА Остатки.Серия.ГоденДо <> ДАТАВРЕМЯ(1, 1, 1)
ТОГДА РАЗНОСТЬДАТ(&ДатаОтчета, Остатки.Серия.ГоденДо, СЕКУНДА) <= 2764800
ИНАЧЕ ЛОЖЬ
КОНЕЦ
На первый взгляд, код выглядит логично: мы проверяем, заполнена ли дата срока годности (ГоденДо), и если она заполнена, вычисляем разницу в секундах между ней и датой отчета. Если эта разница меньше или равна 32 дням (2 764 800 секунд), запись попадает в результат. Тем не менее, при выполнении такого запроса СУБД аварийно завершает работу с ошибкой переполнения.
Функция РАЗНОСТЬДАТ в языке запросов 1С при работе в клиент-серверном режиме транслируется платформой напрямую в T-SQL функцию DATEDIFF на уровне СУБД (например, в MS SQL Server или PostgreSQL).
Согласно технической документации Microsoft SQL Server, результатом работы стандартной функции DATEDIFF является целое число типа INT (знаковое 4-байтовое целое число). Максимальное значение, которое может хранить данный тип данных, составляет 2 147 483 647.
Давайте переведем этот лимит из секунд в более крупные единицы измерения:
Таким образом, если разница между двумя сравниваемыми датами в запросе превышает порог в 68 лет в любую сторону (в прошлое или будущее), СУБД физически не может поместить результат вычислений в тип INT и возвращает ошибку переполнения.
Разработчики часто недоумевают: почему СУБД выдает ошибку, если в конструкции ВЫБОР КОГДА явно прописан обход пустой даты? На это есть две фундаментальные причины:
DATEDIFF) для всех строк таблицы физически, и только после этого применить к результатам условия фильтрации CASE WHEN.01.01.0001 в зависимости от настроек смещения при создании базы (0 или 2000 лет) хранится в SQL как 01.01.1900, 01.01.2001 или (минимальная дата для старых версий SQL Server) 01.01.1753. Попытка вычислить разность в секундах между текущей датой (например, 2026 годом) и 1900 или 1753 годом мгновенно приводит к превышению лимита в 68 лет и вызывает крах запроса еще до фильтрации.01.01.1901 года или 31.12.3999 года. При обработке таких записей ошибка переполнения неизбежна.Вместо того чтобы укрупнять интервалы, существует гораздо более надежное, изящное и производительное решение проблемы — перенос вычислений на сторону константы с помощью функции ДОБАВИТЬКДАТЕ.
Давайте применим базовое математическое правило переноса слагаемых. Наше исходное условие выглядит так:
РАЗНОСТЬДАТ(&ДатаОтчета, Остатки.Серия.ГоденДо, СЕКУНДА) <= 2764800
Это абсолютно тождественно следующему выражению:
Остатки.Серия.ГоденДо <= ДОБАВИТЬКДАТЕ(&ДатаОтчета, СЕКУНДА, 2764800)
Перепишем наш запрос с использованием этой логики:
ВЫБОР
КОГДА Остатки.Серия.ГоденДо <> ДАТАВРЕМЯ(1, 1, 1)
ТОГДА Остатки.Серия.ГоденДо <= ДОБАВИТЬКДАТЕ(&ДатаОтчета, СЕКУНДА, 2764800)
ИНАЧЕ ЛОЖЬ
КОНЕЦ
&ДатаОтчета. Эта операция всегда выполняется успешно.РАЗНОСТЬДАТ(Дата1, ПолеТаблицы), СУБД теряет возможность использовать индекс по этому полю и вынуждена выполнять полное сканирование таблицы (Index Scan или Table Scan). Перенеся функцию на сторону константы (параметра), мы оставляем поле Остатки.Серия.ГоденДо чистым. СУБД мгновенно выполнит эффективный поиск по индексу (Index Seek), что ускорит выполнение запроса в разы или даже в десятки раз на крупных базах данных.Если специфика задачи не требует точности до секунды, самым простым решением будет переход к более крупным единицам измерения времени — дням или часам.
Переведем наши секунды в дни: 2 764 800 секунд / 86 400 секунд в сутках = 32 дня.
Перепишем запрос с расчетом в днях:
ВЫБОР
КОГДА Остатки.Серия.ГоденДо <> ДАТАВРЕМЯ(1, 1, 1)
ТОГДА РАЗНОСТЬДАТ(&ДатаОтчета, Остатки.Серия.ГоденДо, ДЕНЬ) <= 32
ИНАЧЕ ЛОЖЬ
КОНЕЦ
Лимит для разности дат в днях при типе INT составляет более 5,8 миллионов лет. Соответственно, даже если в базе присутствуют битые даты вроде 1753 года или пустые значения со смещением, переполнение физически не сможет произойти.
Если количество секунд рассчитывается динамически в коде 1С, хорошей практикой будет перевести его в дни до передачи параметров в запрос.
Рассмотрим пример реализации на встроенном языке:
// Вычисляем количество дней во встроенном языке
КоличествоСекундИнтервала = 2764800;
КоличествоДнейИнтервала = Цел(КоличествоСекундИнтервала / 86400); // Получаем 32 дня
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Остатки.Серия КАК Серия,
| Остатки.Серия.ГоденДо КАК ГоденДо
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
|ГДЕ
| ВЫБОР
| КОГДА Остатки.Серия.ГоденДо <> ДАТАВРЕМЯ(1, 1, 1)
| ТОГДА РАЗНОСТЬДАТ(&ДатаОтчета, Остатки.Серия.ГоденДо, ДЕНЬ) <= &КоличествоДней
| ИНАЧЕ ЛОЖЬ
| КОНЕЦ";
Запрос.УстановитьПараметр("ДатаОтчета", РабочаяДата);
Запрос.УстановитьПараметр("КоличествоДней", КоличествоДнейИнтервала);
РезультатЗапроса = Запрос.Выполнить();
Этот способ отлично подходит для построения гибких отчетов, где пользователь может самостоятельно задавать интервалы контроля сроков годности или выполнения задач.
СЕКУНДА, МИНУТА) в функции РАЗНОСТЬДАТ, если нет абсолютной уверенности в том, что обе даты заполнены корректно и разница между ними не превышает 68 лет.ДОБАВИТЬКДАТЕ вместо вычисления разности через РАЗНОСТЬДАТ. Это гарантирует отсутствие ошибок переполнения СУБД и значительно ускоряет выполнение запросов за счет использования индексов таблиц.ВЫБОР КОГДА защитит вложенную функцию от вычисления при пустых датах. Оптимизатор СУБД живет по своим законам и может вычислять формулы до применения логического ветвления.