SQL функции даты и времени
Приветствую Вас, уважаемые читатели блога webcodius.ru. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке 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
На этом все, до новых встреч!
Похожие записи
Оставить комментарий
Спасибо вам большое за предоставленную информацию, очень помогли. =)
Это работает только в MySQL, для MS SQL статья бесполезна