В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке SQL есть множество функций связанных с датой и временем, сегодня их и рассмотрим. Все ниже рассмотренные функции работают с календарными типами данных.
Получение текущей даты и времени.
Чтобы получить текущую дату и время используется функция NOW(). Пример:
SELECT NOW() Результат: 2015-09-25 14:42:53
Для получения только текущей даты есть функция CURDATE(). Пример:
SELECT CURDATE() Результат: 2015-09-25
И функция CURTIME(), которая возвращает только текущее время: Пример:
SELECT CURTIME() Результат: 14:42:53
Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, для которых требуется хранить дату добавления. Например, при добавлении статьи на сайт хорошо бы хранить ее дату публикации. Тогда запрос на добавление статьи в базу будет примерно таким:
INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, 'текст статьи', NOW());
Прибавление и вычитание дат и времени
Функция ADDDATE(date, INTERVAL value) прибавляет к дате date значение value и возвращает полученное значение. В качестве value могут выступать следующие значения:
- SECOND - секунды
- MINUTE - минуты
- HOUR - часы
- DAY - дни
- WEEK - недели
- MONTH - месяцы
- QUARTER - кварталы
- YEAR - годы
а также их комбинации:
- MINUTE_SECOND - минуты и секунды
- HOUR_SECONDчасы - минуты и секунды
- HOUR_MINUTE - часы и минуты
- DAY_SECOND - дни, часы, минуты и секунды
- DAY_MINUTE - дни, часы и минуты
- DAY_HOUR - дни и часы
- YEAR_MONTH - года и месяцы.
Пример:
SELECT ADDDATE('2015-09-28 10:30:20', INTERVAL 1 DAY) Результат: 2015-09-29 10:30:20
или
SELECT ADDDATE('2015-09-28 10:30:20', INTERVAL '3 1:20' DAY_MINUTE) Результат: 2015-10-01 11:50:20
Функция SUBDATE(date, INTERVAL value) производит вычитание значения value из даты date. Пример:
SELECT SUBDATE('2015-09-28 10:30:20', INTERVAL 20 HOUR) Результат: 2015-09-27 14:30:20
Функция PERIOD_ADD(period, n) прибавляет к значению period n месяцев. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример:
SELECT PERIOD_ADD(201509, 4) Результат: 201601
Функция TIMESTAMPADD(interval, n, date) прибавляет к дате date временной интервал n, значения которого задаются параметром interval. Возможные значения параметра interval:
- FRAC_SECOND - микросекунды
- SECOND - секунды
- MINUTE - минуты
- HOUR - часы
- DAY - дни
- WEEK - недели
- MONTH - месяцы
- QUARTER - кварталы
- YEAR - годы
Пример:
SELECT TIMESTAMPADD(QUARTER, 1, '2015-09-28') Результат: 2015-12-28
Функция SUBTIME(date, time) вычитает из даты date время time. Пример:
SELECT SUBTIME('2015-09-28 10:30:20', '50:20:19') Результат: 2015-09-26 08:10:01
Вычисление интервала между датами
Функция TIMEDIFF(date1, date2) вычисляет разницу в часах, минутах и секундах между двумя датами date1 и date2. Пример:
SELECT TIMEDIFF('2015-09-28 10:30:20', '2015-09-29 10:30:20') Результат: -24:10:00
Функция DATEDIFF(date1, date2) вычисляет разницу в днях между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример:
SELECT DATEDIFF('2015-09-28 00:00:20', '2015-09-27 23:40:20') Результат: 1
С помощью этой функции легко определить сколько дней прошло с даты публикации статьи:
SELECT DATEDIFF(CURDATE(), date_publication) FROM posts WHERE id_post = 1
Функция PERIOD_DIFF(period1, period2) вычисляет разницу в месяцах между двумя датами. Даты должны быть представлены в формате YYYYMM. Например, узнаем сколько месяцев прошло с января 2015 по сентябрь 2015:
SELECT PERIOD_DIFF(201509, 201501) Результат: 9
Функция TIMESTAMPDIFF(interval, date1, date2) вычисляет разницу между датами date2 и date1 в единицах указанных в параметре interval. При этом interval может принимать следующие значения:
- FRAC_SECOND - микросекунды
- SECOND - секунды
- MINUTE - минуты
- HOUR - часы
- DAY - дни
- WEEK - недели
- MONTH - месяцы
- QUARTER - кварталы
- YEAR - годы
Пример:
SELECT TIMESTAMPDIFF(HOUR, '2015-09-28 10:30:20', '2015-09-28 19:50:20') Результат: 9
Получение различных форматов даты и времени и другой информации
Функция DATE(datetime) возвращает дату, отсекая время. Пример:
SELECT DATE('2015-09-28 10:30:20') Результат: 2015-09-28
Функция TIME(datetime) возвращает время, отсекая дату. Пример:
SELECT TIME('2015-09-28 10:30:20') Результат: 10:30:20
Функция TIMESTAMP(date) возвращает полный формат со временем даты date. Пример:
TIMESTAMP('2015-09-28') Результат: 2015-09-28 00:00:00
DAY(date) и DAYOFMONTH(date). Функции-синонимы, которые возвращают порядковый номер дня месяца. Пример:
SELECT DAY('2015-09-28'), DAYOFMONTH('2015-09-28') Результат: 28 | 28
Функции DAYNAME(date), DAYOFWEEK(date) и WEEKDAY(date). Первая функция возвращает название дня недели, вторая - номер дня недели (отсчет от 1 - воскресенье до 7 - суббота), третья также номер дня недели только другой отсчет(отсчет от 0 - понедельник, до 6 - воскресенье). Пример:
SELECT DAYNAME('2015-09-28'), DAYOFWEEK('2015-09-28'), WEEKDAY('2015-09-28') Результат: Monday 2 | 0
Функции WEEK(date) и WEEKOFYEAR(datetime). Обе функции возвращают номер недели в году, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример:
SELECT WEEK('2015-09-28 10:30:20'), WEEKOFYEAR('2015-09-28 10:30:20') Результат: 39 | 40
Функция MONTH(date) возвращает числовое значение месяца (от 1 до 12), а MONTHNAME(date) название месяца. Пример:
SELECT MONTH('2015-09-28 10:30:20'), MONTHNAME('2015-09-28 10:30:20') Результат: 9 | September
Функция QUARTER(date) возвращает номер квартала года (от 1 до 4). Пример:
SELECT QUARTER('2015-09-28 10:30:20') Результат: 3
Функция YEAR(date) возвращает значение года (от 1000 до 9999). Пример:
SELECT YEAR('2015-09-28 10:30:20') Результат: 2015
Функция DAYOFYEAR(date) возвращает порядковый номер дня в году (от 1 до 366). Прмиер:
SELECT DAYOFYEAR('2015-09-28 10:30:20') Результат: 271
Функция HOUR(datetime) возвращает значение часа (от 0 до 23). Пример:
SELECT HOUR('2015-09-28 10:30:20') Результат: 10
Функция MINUTE(datetime) возвращает значение минут (от 0 до 59). Пример:
SELECT MINUTE('2015-09-28 10:30:20') Результат: 30
Функция SECOND(datetime) возвращает значение секунд (от 0 до 59). Пример:
SELECT SECOND('2015-09-28 10:30:20') Результат: 20
Функция EXTRACT(type FROM date) возвращает часть даты date определяемую параметром type. Пример:
SELECT EXTRACT(YEAR FROM '2015-09-28 10:30:20'), EXTRACT(MONTH FROM '2015-09-28 10:30:20'), EXTRACT(DAY FROM '2015-09-28 10:30:20'), EXTRACT(HOUR FROM '2015-09-28 10:30:20'), EXTRACT(MINUTE FROM '2015-09-28 10:30:20'), EXTRACT(SECOND FROM '2015-09-28 10:30:20') Результат: 2015 | 9 | 28 | 10 | 30 | 20
Взаимообратные функции TO_DAYS(date) и FROM_DAYS(n). Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату. Пример:
SELECT TO_DAYS('2015-09-28 10:30:20'), FROM_DAYS(736234) Результат: 736234 | 2015-09-28
Взаимообратные функции UNIX_TIMESTAMP(date) и FROM_UNIXTIME(n). Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату. Пример:
SELECT UNIX_TIMESTAMP('2015-09-28 10:30:20'), FROM_UNIXTIME(1443425420) Результат: 1443425420 | 2015-09-28 10:30:20
Взаимообратные функции TIME_TO_SEC(time) и SEC_TO_TIME(n). Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример:
SELECT TIME_TO_SEC('10:30:20'), SEC_TO_TIME(37820) Результат: 37820 | 10:30:20
Функция MAKEDATE(year, n) принимает год year и номер дня в году n и преобразует их в дату. Пример:
SELECT MAKEDATE(2015, 271) Результат: 2015-09-28
На этом все, до новых встреч!
