Функции Transact-SQL для работы со строками и данными типа даты/времени

Функции T-SQL для работы со значениями даты и времени

Задача. Посчитать число минут в интервале между двумя датами – '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');

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

    Схема

Нет схемы

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

MySQL

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

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

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

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

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

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

PostgreSQL

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

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

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

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

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

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

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

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

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

EXTRACT(EPOCH FROM < interval >)

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

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

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

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

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;

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