Вход
testopay.com

SQL функции даты и времени

birds
Администратор

Сообщений: 646

Благодарности: 99

На форуме: с 02.03.20

06 апр 2022   15:35

В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке 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

На этом все, до новых встреч!