Мы сталкиваемся с распространенной ситуацией, когда функция 1С РАЗНОСТЬДАТ() не имеет прямого аналога в PostgreSQL в виде привычной функции DATEDIFF(), как, например, в MS SQL Server или MySQL. Это вызывает вопросы о том, как запросы 1С транслируются в SQL для PostgreSQL и как мы можем эффективно работать с разницей дат и времени. В этой статье мы подробно рассмотрим механизмы трансляции 1С, а также предложим универсальное решение на основе пользовательской функции.
Когда мы используем функцию РАЗНОСТЬДАТ() в запросах 1С для получения разницы в секундах, платформа 1С обычно транслирует этот вызов в оператор вычитания для типов timestamp PostgreSQL с последующим извлечением "эпохи" (общего количества секунд) из получившегося интервала. Рассмотрим пример, который был представлен в обсуждении:
ВЫБРАТЬ РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2002, 12, 31, 10, 20, 34), ДАТАВРЕМЯ(2003, 01, 01, 9, 18, 06), СЕКУНДА)
Этот запрос 1С, при работе с PostgreSQL, транслируется в следующий SQL-запрос:
SELECT date_part('epoch','2003-01-01 09:18:06'::timestamp - '2002-12-31 10:20:34'::timestamp)::numeric(15,0)
Давайте разберем этот SQL-запрос по шагам:
'2003-01-01 09:18:06'::timestamp - '2002-12-31 10:20:34'::timestamp выполняет прямое вычитание одного значения типа timestamp из другого. В PostgreSQL результатом такого вычитания является тип данных interval (интервал). Например, результат может выглядеть как 1 day 01:00:00.date_part('epoch', ...) извлекает из полученного интервала общее количество секунд. "Epoch" в данном контексте означает общее количество секунд, прошедших с начала эпохи Unix (1 января 1970 года 00:00:00 UTC), но примененное к интервалу, оно возвращает общее количество секунд, содержащихся в этом интервале.numeric(15,0) с помощью ::numeric(15,0). Это гарантирует, что результат будет целым числом, соответствующим требованиям 1С.Этот подход является стандартным и весьма эффективным для точных единиц измерения времени, таких как секунды, минуты и часы, которые могут быть легко получены из общего количества секунд. Мы можем легко адаптировать его для минут (разделив на 60) или часов (разделив на 3600).
Для более сложных единиц измерения, таких как месяцы, кварталы или годы, а также для унификации поведения функции РАЗНОСТЬДАТ(), платформа 1С может использовать пользовательские функции, определенные непосредственно в PostgreSQL. В обсуждаемой теме был показан пример, когда для вычисления разности в месяцах 1С генерирует вызов функции DATEDIFF2():
ВЫБРАТЬ РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2022, 12, 31, 00, 00, 00), ДАТАВРЕМЯ(2003, 01, 01, 9, 18, 07), МЕСЯЦ)
Этот запрос 1С транслируется в следующий SQL для PostgreSQL:
SELECT DATEDIFF2('MONTH','2022-12-31 00:00:00'::timestamp,'2003-01-01 09:18:07'::timestamp)::numeric(15, 0)
Это означает, что на стороне базы данных PostgreSQL должна быть создана соответствующая функция public.datediff2. Мы рассмотрим ее реализацию, которая была предоставлена участниками форума.
Код пользовательской функции DATEDIFF2 на PL/pgSQL:
CREATE OR REPLACE FUNCTION public.datediff2(
character varying,
timestamp without time zone,
timestamp without time zone)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS
$BODY$
DECLARE
arg_mode alias for $1;
arg_d2 alias for $2;
arg_d1 alias for $3;
BEGIN
if arg_mode = 'SECOND' then
return date_part('epoch',arg_d1) - date_part('epoch',arg_d2) ;
elsif arg_mode = 'MINUTE' then
return trunc((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) / 60);
elsif arg_mode = 'HOUR' then
return trunc((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) /3600);
elsif arg_mode = 'DAY' then
return cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = 'WEEK' then
return trunc( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = 'MONTH' then
return 12 * (date_part('year',arg_d1) - date_part('year',arg_d2))
+ date_part('month',arg_d1) - date_part('month',arg_d2);
elsif arg_mode = 'QUARTER' then
return 4 * (date_part('year',arg_d1) - date_part('year',arg_d2))
+ date_part('quarter',arg_d1) - date_part('quarter',arg_d2);
elsif arg_mode = 'YEAR' then
return (date_part('year',arg_d1) - date_part('year',arg_d2));
end if;
END
$BODY$;
Давайте проанализируем эту функцию, чтобы понять ее логику и применение:
CREATE OR REPLACE FUNCTION public.datediff2(...): Мы создаем или заменяем функцию с именем datediff2 в схеме public.character varying: Строка, указывающая единицу измерения, например, 'SECOND', 'MONTH', 'DAY'.timestamp without time zone (дважды): Две отметки даты и времени, между которыми нужно найти разницу. Внутри функции они объявляются как arg_d2 и arg_d1 соответственно. Важно отметить, что в 1С порядок аргументов РАЗНОСТЬДАТ(ДатаВремя1, ДатаВремя2, ТипРазности), а в SQL-трансляции для DATEDIFF2 часто используется DATEDIFF2(тип, ДатаВремя2, ДатаВремя1), поэтому в теле функции используются arg_d1 и arg_d2 для обозначения второй и первой даты.RETURNS integer: Функция возвращает целое число, представляющее разницу.LANGUAGE 'plpgsql': Указывает, что функция написана на процедурном языке PL/pgSQL.'SECOND': return date_part('epoch',arg_d1) - date_part('epoch',arg_d2) ;. Здесь мы используем тот же принцип, что и при прямой трансляции 1С для секунд – вычитаем значения epoch (общее количество секунд) из двух дат.'MINUTE' и 'HOUR': Разница в epoch делится на соответствующий коэффициент (60 для минут, 3600 для часов). Функция trunc() используется для отбрасывания дробной части, что соответствует подсчету полных интервалов (например, 59 минут не считается как полная минута).return cast(arg_d1 as date) - cast(arg_d2 as date);. Для разницы в днях используется прямое вычитание дат, приведенных к типу date. PostgreSQL автоматически возвращает целое число, представляющее количество дней между двумя датами.return trunc( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);. Разница в днях делится на 7, и результат также округляется в меньшую сторону с помощью trunc() для получения полных недель.
return 12 * (date_part('year',arg_d1) - date_part('year',arg_d2))
+ date_part('month',arg_d1) - date_part('month',arg_d2);
Создание и использование такой пользовательской функции позволяет нам гибко настроить поведение РАЗНОСТЬДАТ() для различных единиц измерения, обеспечивая соответствие требованиям 1С и специфике PostgreSQL.
Хотя пользовательская функция DATEDIFF2, как мы выяснили, покрывает большинство потребностей, полезно знать и другие встроенные возможности PostgreSQL для работы с датами и временем. Они могут быть применены напрямую в SQL-запросах, минуя 1С, или использованы для разработки более специализированных функций.
TIMESTAMP и DATE:
Когда мы вычитаем один timestamp из другого, результатом является тип interval (интервал). Мы уже видели этот механизм при трансляции разности в секундах. Например:
SELECT '2023-01-02 10:00:00'::timestamp - '2023-01-01 09:00:00'::timestamp;
-- Результат: 1 day 01:00:00
При вычитании двух значений типа date, PostgreSQL сразу возвращает целое число, представляющее количество дней между датами. Это самый простой способ получить разницу в днях:
SELECT '2023-01-05'::date - '2023-01-01'::date;
-- Результат: 4
EXTRACT() и DATE_PART():
Эти функции являются мощными инструментами для извлечения различных компонентов (полей) из значений timestamp, date или interval. Они позволяют нам получить год, месяц, день, час, минуту, секунду, а также EPOCH (общее количество секунд).
Например, чтобы получить общее количество секунд из интервала, мы можем использовать:
SELECT EXTRACT(EPOCH FROM ('2023-01-02 10:00:00'::timestamp - '2023-01-01 09:00:00'::timestamp));
-- Результат: 90000 (секунд)
Функция DATE_PART() является стандартом SQL и предлагает схожий функционал:
SELECT DATE_PART('epoch', '2023-01-02 10:00:00'::timestamp - '2023-01-01 09:00:00'::timestamp);
-- Результат: 90000
Мы можем использовать их не только с интервалами, но и непосредственно с датами, чтобы получить, например, номер месяца или квартала.
AGE():
Функция AGE(timestamp1, timestamp2) возвращает интервал, представляющий разницу между двумя отметками времени. Она похожа на вычитание timestamp1 - timestamp2, но всегда возвращает положительный результат, если первый аргумент больше второго. Часто используется для вычисления возраста.
SELECT AGE('2023-01-01', '2022-01-01');
-- Результат: 1 year
Понимание этих встроенных функций и операторов PostgreSQL позволяет нам более глубоко анализировать и оптимизировать запросы, а также создавать собственные эффективные решения.
При использовании пользовательских функций, таких как DATEDIFF2, критически важно убедиться, что логика подсчета разницы в месяцах, кварталах и годах соответствует вашим бизнес-правилам. Разные СУБД, а также разные реализации одной и той же функции, могут по-разному интерпретировать "полный" интервал. Например, рассмотрим следующие сценарии с датами:
DATEDIFF2, которая считает полные месяцы): 0 месяцев. Поскольку date_part('month', '2023-02-01') = 2 и date_part('month', '2023-01-31') = 1, разница будет 1. Однако, поскольку год не изменился, а месяцы 2 и 1, то 2-1 = 1 месяц. Если бы мы меняли порядок дат, то получили бы отрицательное значение. Важно, что функция считает "целые" месяцы на основе порядковых номеров месяцев и лет, а не по дням.DATEDIFF2): 1 месяц (2 - 1 = 1).DATEDIFF2): 1 месяц.Мы видим, что логика DATEDIFF2 для месяцев, кварталов и годов основана на разнице соответствующих компонентов даты (год, месяц, квартал), а не на продолжительности интервала в днях или секундах. Это стандартный подход для подобных функций, но он может не всегда соответствовать интуитивному пониманию "прошедших" интервалов, если мы ожидаем подсчет по дням. Поэтому всегда проверяйте, как функция ведет себя на граничных случаях и при различных входных данных, чтобы избежать неверных расчетов и убедиться, что ее поведение соответствует вашим бизнес-требованиям.
Таким образом, для работы с функцией РАЗНОСТЬДАТ() в 1С при использовании PostgreSQL мы видим двойной подход: для точных единиц времени платформа генерирует встроенные операторы и функции PostgreSQL (такие как вычитание timestamp с последующим использованием date_part('epoch', ...)), а для более сложных единиц, таких как месяцы и годы, используется вспомогательная пользовательская функция DATEDIFF2. Понимая эти механизмы и имея под рукой реализацию DATEDIFF2, мы можем уверенно работать с датами и временем в связке 1С и PostgreSQL, обеспечивая корректность и производительность запросов. Всегда помните о важности проверки логики пользовательских функций на соответствие вашим специфическим требованиям.