Главная → Уроки SQL → Урок 13. Функции даты и времени

SQL - Урок 13. Функции даты и времени

Эти функции предназначены для работы с календарными типами данных. Рассмотрим наиболее применимые.
  • CURDATE(), CURTIME() и NOW() Первая функция возвращает текущую дату, вторая - текущее время, а третья - текущую дату и время. Сравните:
    SELECT CURDATE(), CURTIME(), NOW();
    Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее время. В нашем магазине все поставки и продажи используют текущее время. Поэтому для добавления записей о поставах, и продажах удобно использовать функцию CURDATE(). Например, пусть в наш магазин пришел товар, давайте добавим информацию об этом в таблицу Поставка (incoming):
    INSERT INTO incoming (id_vendor, date_incoming) VALUES ('2', curdate());
    Если бы мы хранили дату поставки с типом datatime, то нам больше подошла бы функция NOW().

  • ADDDATE(date, INTERVAL value) Функция возвращает дату date, к которой прибавлено значение value. Значение value может быть отрицательным, тогда итоговая дата уменьшится. Давайте посмотрим, когда наши поставщики делали поставки товара:
    SELECT id_vendor, date_incoming FROM incoming;
    Предположим, мы ошиблись при вводе даты для первого поставщика, давайте уменьшим его дату на одни сутки:
    SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 DAY) FROM incoming WHERE id_vendor=1;
    В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR). Давайте для пример уменьшим дату поставки для второго поставщика на 1 неделю:
    SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 WEEK) FROM incoming WHERE id_vendor=2;
    В нашей таблице Поставки (incoming) мы использовали для столбца Дата поставки (date_incoming) тип date. Как вы помните из урока 2, этот тип данных предназначен для хранения только даты. А вот если бы мы использовали тип datatime, то у нас отображалась бы не только дата, но и время. Тогда мы могли бы использовать функцию ADDDATE и для времени. В качестве значения value в этом случае могут выступать секунды (SECOND), минуты (MINUTE), часы (HOUR) и их комбинации:
    минуты и секунды (MINUTE_SECOND),
    часы, минуты и секунды (HOUR_SECOND),
    часы и минуты (HOUR_MINUTE),
    дни, часы, минуты и секунды (DAY_SECOND),
    дни, часы и минуты (DAY_MINUTE),
    дни и часы (DAY_HOUR),
    года и месяцы (YEAR_MONTH).
    Например, давайте к дате 15 апреля 2011 года две минуты первого прибавим 2 часа 45 минут:
    SELECT ADDDATE('2011-04-15 00:02:00', INTERVAL '02:45' HOUR_MINUTE);

  • SUBDATE(date, INTERVAL value) функция идентична предыдущей, но производит операцию вычитания, а не сложения.
    SELECT SUBDATE('2011-04-15 00:02:00', INTERVAL '23:53' HOUR_MINUTE);

  • PERIOD_ADD(period, n) функция добавляет n месяцев к значению даты period. Нюанс: значение даты должно быть представлено в формате YYYYMM. Давайте к февралю 2011 (201102) прибавим 2 месяца:
    SELECT PERIOD_ADD(201102, 2);

  • TIMESTAMPADD(interval, n, date) функция добавляет к дате date временной интервал n, значения которого задаются параметром interval. Возможные значения параметра interval:

    FRAC_SECOND - микросекунды
    SECOND - секунды
    MINUTE - минуты
    HOUR - часы
    DAY - дни
    WEEK - недели
    MONTH - месяцы
    QUARTER - кварталы
    YEAR - годы
    SELECT TIMESTAMPADD(DAY, 2, '2011-04-02');

  • TIMEDIFF(date1, date2) вычисляет разницу в часах, минутах и секундах между двумя датами.
    SELECT TIMEDIFF('2011-04-17 23:50:00', '2011_04-16 14:50:00');

  • DATEDIFF(date1, date2) вычисляет разницу в днях между двумя датами. Например, мы хотим узнать, как давно поставщик 'Вильямс' (id=1) поставлял нам товар:
    SELECT date_incoming, CURDATE(), DATEDIFF(CURDATE(), date_incoming) FROM incoming WHERE id_vendor=1;

  • PERIOD_DIFF(period1, period2) функция вычисляет разницу в месяцах между двумя датами, представленными в формате YYYYMM. Давайте узнаем разницу между январем 2010 и августом 2011:
    SELECT PERIOD_DIFF(201108, 201001);

  • TIMESTAMPDIFF(interval, date1, date2) функция вычисляет разницу между датами date2 и date1 в единицах, указанных в параметре interval. Возможные значения параметра interval:

    FRAC_SECOND - микросекунды
    SECOND - секунды
    MINUTE - минуты
    HOUR - часы
    DAY - дни
    WEEK - недели
    MONTH - месяцы
    QUARTER - кварталы
    YEAR - годы
    SELECT TIMESTAMPDIFF(DAY, '2011-04-02', '2011-04-17') AS days, TIMESTAMPDIFF(HOUR, '2011-04-16 20:14:00', '2011-04-17 23:58:20') AS houres;

  • SUBTIME(date, time) функция вычитает из времени date время time:
    SELECT SUBTIME('2011-04-18 23:17:00', '02:15:30');

  • DATE(datetime) возвращает дату, отсекая время. Например:
    SELECT DATE('2011-04-15 00:03:20');

  • TIME(datetime) возвращает время, отсекая дату. Например:
    SELECT TIME('2011-04-15 00:03:20');

  • TIMESTAMP(date) функция принимает дату date и возвращает полный вариант со временем. Например:
    SELECT TIMESTAMP('2011-04-17');

  • DAY(date) и DAYOFMONTH(date) функции-синонимы, возвращают из даты порядковый номер дня месяца:
    SELECT DAY('2011-04-17'), DAYOFMONTH('2011-04-17');

  • DAYNAME(date), DAYOFWEEK(date) и WEEKDAY(date) функции возвращают день недели, первая - его название, вторая - номер дня недели (отсчет от 1 - воскресенье до 7 - суббота), третья - номер дня недели (отсчет от 0 - понедельник, до 6 - воскресенье:
    SELECT DAYNAME('2011-04-17'), DAYOFWEEK('2011-04-17'), WEEKDAY('2011-04-17');

  • WEEK(date), WEEKOFYEAR(datetime) обе функции возвращают номер недели в году, первая для типа date, а вторая - для типа datetime, у первой неделя начинается с воскресенья, у второй - с понедельника:
    SELECT WEEK('2011-04-17'), WEEKOFYEAR('2011-04-17 23:40:00');

  • MONTH(date) и MONTHNAME(date) обе функции возвращают значения месяца. Первая - его числовое значение (от 1 до 12), вторая - название месяца:
    SELECT MONTH('2011-04-17'), MONTHNAME('2011-04-17');

  • QUARTER(date) функция возвращает значение квартала года (от 1 до 4):
    SELECT QUARTER('2011-04-17');

  • YEAR(date) функция возвращает значение года (от 1000 до 9999):
    SELECT YEAR('2011-04-17');

  • DAYOFYEAR(date) возвращает порядковый номер дня в году (от 1 до 366):
    SELECT DAYOFYEAR('2011-04-17');

  • HOUR(datetime) возвращает значение часа для времени (от 0 до 23):
    SELECT HOUR('2011-04-17 18:20:03');

  • MINUTE(datetime) возвращает значение минут для времени (от 0 до 59):
    SELECT MINUTE('2011-04-17 18:20:03');

  • SECOND(datetime) возвращает значение секунд для времени (от 0 до 59):
    SELECT SECOND('2011-04-17 18:20:03');

  • EXTRACT(type FROM date) возвращает часть date определяемую параметром type:
    SELECT EXTRACT(YEAR FROM '2011-04-17 23:15:18') AS year, EXTRACT(MONTH FROM '2011-04-17 23:15:18') AS mon, EXTRACT(DAY FROM '2011-04-17 23:15:18') AS day, EXTRACT(HOUR FROM '2011-04-17 23:15:18') AS hour, EXTRACT(MINUTE FROM '2011-04-17 23:15:18') AS min, EXTRACT(SECOND FROM '2011-04-17 23:15:18') AS sec;

  • TO_DAYS(date) и FROM_DAYS(n) взаимообратные функции. Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату:
    SELECT TO_DAYS('2011-04-17'), FROM_DAYS(734420);

  • UNIX_TIMESTAMP(date) и FROM_UNIXTIME(n) взаимообратные функции. Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату:
    SELECT UNIX_TIMESTAMP('2011-04-17'), FROM_UNIXTIME(1302524000);

  • TIME_TO_SEC(time) и SEC_TO_TIME(n) взаимообратные функции. Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время:
    SELECT TIME_TO_SEC('22:10:30'), SEC_TO_TIME(45368);

  • MAKEDATE(year, n) функция принимает год и номер дня в году и преобразует их в дату:
    SELECT MAKEDATE(2011, 120);

Ну вот, на сегодня все. В следующий раз рассмотрим функции, которые помогают переводить даты из одного формата в другой.



Предыдущий урок Вернуться в раздел Следующий урок 
Программирование на Python для начинающих


Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.