Функция DATEPART

Синтаксис:

DATEPART(datepart , date)

Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date).

Список допустимых значений аргумента datepart, описанный выше в данном разделе, дополняется еще одним значением

Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник — день тяжелый, а для кого-то — воскресенье. Как и многие региональные настройки, по умолчанию это значение в SQL Server зависит от выбранного языка.

Однако вернемся к примеру 7.1.3. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу — минутам. Итак, время вылета рейса 1123 в минутах:

SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out)
FROM Trip
WHERE trip_no = 1123;

и время прилета

SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in)
FROM Trip
WHERE trip_no = 1123;

Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, следует вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).

SELECT CASE
          WHEN time_dep >= time_arr
          THEN time_arr - time_dep + 1440
          ELSE time_arr - time_dep
       END dur
FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep,
             DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr
      FROM Trip
      WHERE trip_no = 1123
     ) tm;

Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.

Пример 7.1.4

В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, так как в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip

SELECT DISTINCT pt.trip_no, 
                DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time]
FROM Pass_in_trip pt 
    JOIN Trip t ON pt.trip_no = t.trip_no
WHERE t.trip_no = 1123;

Выполнив запрос, получим следующий результат

DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в таблице Pass_in_trip для каждого пассажира данного рейса.

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

DAY(date) — целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date).

MONTH(date) — целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date).

YEAR(date) — целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).

Рекомендуемые упражнения: 43, 75, 110, 119, 143

MySQL

В MySQL для извлечения каждой составляющей даты имеется соответствующая функция. Вот так, например, можно получить минуты времени отправления рейсов, которые вылетают в 1-ом часу дня (база данных “Аэропорт”):

Решение 1

select time_out, MINUTE(time_out)   
from trip   
where HOUR(time_out) = 12;

Компоненту времени можно также получить при помощи функции EXTRACT. С помощи этой функции решение задачи можно переписать в виде:

Решение 2

select time_out, EXTRACT(MINUTE FROM time_out) AS min   
from trip   
where EXTRACT(HOUR FROM time_out) = 12;

Дополнительно с помощью функции EXTRACT можно получить составные компоненты даты/времени, например, год и месяц. Решим такую задачу.

Решение 3

select YEAR(b_datetime) AS y, MONTH(b_datetime) AS m,
       EXTRACT(YEAR_MONTH FROM b_datetime) AS ym, count(*) AS qty  
from utb  
group by EXTRACT(YEAR_MONTH FROM b_datetime);

Полный список допустимых компонент можно найти, например, здесь.

PostgreSQL

Функций типа Year, Month и т.д. в PostgreSQL, насколько мне известно, нет. Однако есть функция EXTRACT, и второе решение первой задачи, которое мы написали для MySQL, будет работать и под этой СУБД:

select time_out, EXTRACT(MINUTE FROM time_out) AS min   
from trip   
where EXTRACT(HOUR FROM time_out) = 12;

Имеется также функция, аналогичная DATEPART в MSSQL. Различия в синтаксисе, надеюсь, станут очевидны из примера решения той же задачи с использованием этой функции:

select time_out, DATE_PART('MINUTE', time_out) AS min   
from trip   
where DATE_PART('HOUR', time_out) = 12;

Перейдем к решению второй задачи. Составные компоненты не поддерживаются в функции EXTRACT для PostgreSQL. Поэтому можно использовать “классическую” групировку по двум компонентам - году и месяцу:

Решение 4

select EXTRACT(YEAR FROM b_datetime) AS y,
       EXTRACT(MONTH FROM b_datetime) AS m, 
       count(*) AS qty
from utb  
group by EXTRACT(YEAR FROM b_datetime), EXTRACT(MONTH FROM b_datetime);

Тем не менее, мы можем реализовать идею составной компоненты при помощи функции форматирования даты TO_CHAR:

select TO_CHAR(b_datetime, 'YYYYMM') AS ym, count(*) AS qty  
from utb  
group by TO_CHAR(b_datetime, 'YYYYMM');

Результат будет аналогичен результату решения 3 за отсутствием первых двух столбцов, которые, разумеется, можно добавить в вывод, включив их одновременно в предложение GROUP BY, как это сделано в решении 4.