Функция DATEDIFF

Синтаксис:

DATEDIFF(datepart, startdate, enddate)

Функция возвращает интервал времени, прошедшего между двумя временными отметками — startdate (начальная отметка) и enddate (конечная отметка). Этот интервал может быть измерен в разных единицах. Возможные варианты определяются аргументом datepart и перечислены выше применительно к функции DATEADD.

Пример 7.1.2

Определить количество дней, прошедших между первым и последним совершенными рейсами.

SELECT DATEDIFF(dd, 
                (SELECT MIN(date)
                FROM pass_in_trip
                ),
                (SELECT MAX(date)
                FROM pass_in_trip
                )
               );

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Пример 7.1.3

Определить продолжительность рейса 1123 в минутах.

Здесь следует принять во внимание, что время вылета (time_out) и время прилета (time_in) хранится в полях типа datetime таблицы Trip. Как уже отмечалось, SQL Server вплоть до версии 2005 не имел отдельных темпоральных типов данных для даты и времени. Поэтому при вставке в поле datetime только времени (например, UPDATE trip SET time_out = ’17:24:00’ WHERE trip_no = 1123), время будет дополнено значением даты по умолчанию (1900-01-01), являющейся начальной точкой отсчета времени.

Напрашивающееся решение

SELECT DATEDIFF(mi, time_out, time_in) dur
FROM trip
WHERE trip_no = 1123;

🚫

[[ error ]]

[[ column ]]
[[ value ]]

(которое дает -760 минут) будет неверным по двум причинам.

Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным;

Во-вторых, ненадежно делать какие-либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime.

Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место.

Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло?

Здесь может помочь функция T-SQL DATEPART, речь о которой пойдет в следующем пункте.

Следует иметь в виду одну особенность использования функции DATEDIFF. Начнем с примеров. Сначала посчитаем число недель с воскресенья 23-10-2005 до субботы 29-10-2005. Итак,

SELECT DATEDIFF(wk, '20051023', '20051029');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Здравый смысл подсказывает, что это полная неделя, однако, вышеприведенный запрос дает 0. Теперь возьмем интервал с субботы 29-10-2005 до воскресенья 30-10-2005:

SELECT DATEDIFF(wk, '20051029', '20051030');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

В результате получим 1, то есть одну неделю. Пора дать объяснения. Дело в том, что функция DATEDIFF фактически считает неделей не число дней, а число переходов с субботы на воскресенье. Если это иметь в виду, то тогда становится понятным еще более удивительный пример:

SELECT DATEDIFF(wk, '20051029 23:59:00', '20051030 00:01:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

который тоже дает единицу!

Странно? Возможно, но, как говорится, кто предупрежден, тот вооружен. То же самое имеет место и для других интервалов. Например, количество дней дает нам не число часов, деленное нацело на 24 (количество часов в сутках), а число переходов через полночь. Хотите подтверждения? Пожалуйста

SELECT DATEDIFF(dd, '20051029 23:59:00', '20051030 00:01:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

В результате получаем один день, в то же время

SELECT DATEDIFF(dd, '20051029 00:00:00', '20051029 23:59:59');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

дает нам 0.

Если хотите, можете продолжить эксперименты с другими временными интервалами. Вы можете воспользоваться для этого предоставляемой Консолью.

Рекомендуемые упражнения: 76, 93

Задача. Посчитать число минут в интервале между двумя датами – ‘2011-10-07 23:43:00’ и ‘2011-10-08 01:23:00’

SQL Server

Встроенная функция DATEDIFF решает проблему:

SELECT DATEDIFF (minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Результат – 100 минут.

Замечание

В запросе используется стандартное представление даты (ISO) в виде текстовой строки ‘yyyy-mm-ddThh:mm:ss’, которое не зависит ни от локальных настроек сервера, ни и от самого сервера.

MySQL

Функция DATEDIFF есть и в MySQL, однако она имеет совсем другой смысл. DATEDIFF вычисляет число дней между двумя датами, являющихся аргументами этой функции. При этом если дата представлена в формате дата-время, используется только составляющая даты. Поэтому все три нижепредставленных запроса дадут один и тот же результат -1. Положительный результат будет получен, если первый аргумент больше второго.

SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08T01:23:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

SELECT DATEDIFF('2011-10-07', '2011-10-08');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Один день мы получим даже в случае, если интервал между датами составляет все одну секунду:

SELECT DATEDIFF('2011-10-07T23:59:59', '2011-10-08T00:00:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Решение же нашей задачи можно получить при помощи другой встроенной функции – TIMESTAMPDIFF, которая аналогичная функции DATEDIFF в SQL Server:

SELECT TIMESTAMPDIFF(minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

PostgreSQL

В PostgreSQL нет функции, подобной DATEDIFF (SQL Server) или TIMESTAMPDIFF (MySQL). Поэтому для решения задачи можно применить следующую последовательность действий:

представить разность между двумя датами интервалом;
посчитать число секунд в интервале;
поделить полученное на шаге 2 значение на 60, чтобы выразить результат в минутах.

Для получения интервала можно взять разность двух значений темпорального типа, При этом требуется явное преобразование типа:

SELECT timestamp '2011-10-08T01:23:00' - timestamp '2011-10-07T23:43:00';

🚫

[[ error ]]

[[ column ]]
[[ value ]]

или в стандартном исполнении

SELECT cast('2011-10-08T01:23:00' AS timestamp) - cast('2011-10-07T23:43:00' AS timestamp);

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Результат - “01:40:00”, который есть не что иное, как один час и сорок минут.

Можно также воспользоваться встроенной функцией AGE, которая выполняет заодно неявное преобразование типа своих аргументов:

SELECT AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:00');

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Для получения числа секунд в интервале воспользуемся функцией

EXTRACT(EPOCH FROM <interval>)

Значение интервала представим последним способом как наиболее коротким. Для получения окончательного решения задачи поделим полученный результат на 60:

SELECT EXTRACT(EPOCH FROM AGE('2011-10-08T01:23:00', '2011-10-07T23:43:00'))/60;

🚫

[[ error ]]

[[ column ]]
[[ value ]]

В данном случае мы имеем результат, выраженный целым числом, поскольку оба значения времени в задаче не содержали секунд. В противном случае, будет получено десятичное число, например: 99.75:

SELECT EXTRACT(EPOCH FROM AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:15'))/60;

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Oracle

Oracle тоже не имеет в своем арсенале функции типа DATEDIFF. Кроме того, Oracle не поддерживает стандартное текстовое представление даты/времени.

Мы можем посчитать интервал в минутах, приняв во внимание, что разность дат (значений типа date) в Oracle дает в результате число дней (суток). Тогда для вычисления интервала в минутах нужно просто разность дат умножить на 24 (число часов в сутках), а затем на 60 (число минут в часе):

select (cast('2011-10-08 01:23:00' as date) - cast('2011-10-07 23:43:00' as date))*24*60
from dual;

🚫

[[ error ]]

[[ column ]]
[[ value ]]

Подобным образом можно получить и другие временные интервалы.

#DATEDIFF #Функция #Oracle #PostgreSQL #MySQL #TIMESTAMPDIFF #TIMESTAMP #AGE #EXTRACT #EPOCH

Функция DATEADD Функция DATEPART