sql now минус день
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 могут выступать следующие значения:
а также их комбинации:
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
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
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
Вычисление интервала между датами
Функция 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
SELECT PERIOD_DIFF (201509, 201501)
Результат: 9
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 (‘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
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: работа с датами и временем
В этой статье мы рассмотрим основы работы с датой и временем в MySQL.
Формат даты и времени
MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:
DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.
Создание полей даты и времени
Форматы даты и времени
Наиболее часто используемым разделителем для дат является тире ( — ), а для времени — двоеточие ( : ). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.
Например, все следующие форматы являются правильными:
Функции даты и времени
MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:
| Функция | Описание |
| ADDDATE() | Добавляет дату. |
| ADDTIME() | Добавляет время. |
| CONVERT_TZ() | Конвертирует из одного часового пояса в другой. |
| CURDATE() | Возвращает текущую дату. |
| CURTIME() | Возвращает текущее системное время. |
| DATE_ADD() | Добавляет одну дату к другой. |
| MySQL DATE FORMAT () | Задает указанный формат даты. |
| DATE() | Извлекает часть даты из даты или выражения дата-время. |
| DATEDIFF() | Вычитает одну дату из другой. |
| DAYNAME() | Возвращает день недели. |
| DAYOFMONTH() | Возвращает день месяца (1-31). |
| DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
| DAYOFYEAR() | Возвращает день года (1-366). |
| EXTRACT | Извлекает часть даты. |
| FROM_DAYS() | Преобразует номер дня в дату. |
| FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
| MySQL DATE_SUB () | Вычитает одну дату из другой. |
| HOUR() | Извлекает час. |
| LAST_DAY | Возвращает последний день месяца для аргумента. |
| MAKEDATE() | Создает дату из года и дня года. |
| MAKETIME () | Возвращает значение времени. |
| MICROSECOND() | Возвращает миллисекунды из аргумента. |
| MINUTE() | Возвращает минуты из аргумента. |
| MONTH() | Возвращает месяц из переданной даты. |
| MONTHNAME() | Возвращает название месяца. |
| NOW() | Возвращает текущую дату и время. |
| PERIOD_ADD() | Добавляет интервал к месяцу-году. |
| PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
| QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
| SEC_TO_TIME() | Конвертирует секунды в формат ‘ЧЧ:MM:СС’. |
| SECOND() | Возвращает секунду (0-59). |
| MySQL STR TO DATE () | Преобразует строку в дату. |
| SUBTIME() | Вычитает время. |
| SYSDATE() | Возвращает время, в которое была выполнена функция. |
| TIME_FORMAT() | Задает формат времени. |
| TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
| TIME() | Выбирает часть времени из выражения, передаваемого в качестве аргумента. |
| TIMEDIFF() | Вычитает время. |
| TIMESTAMP() | С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов. |
| TIMESTAMPADD() | Добавляет интервал к дате-времени. |
| TIMESTAMPDIFF() | Вычитает интервал из даты — времени. |
| TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
| UNIX_TIMESTAMP() | Извлекает дату-время в формате UNIX в формат, принимаемый MySQL. |
| UTC_DATE() | Возвращает текущую дату по универсальному времени (UTC). |
| UTC_TIME() | Возвращает текущее время по универсальному времени (UTC). |
| UTC_TIMESTAMP() | Возвращает текущую дату-время по универсальному времени (UTC). |
| WEEK() | Возвращает номер недели. |
| WEEKDAY() | Возвращает индекс дня недели. |
| WEEKOFYEAR() | Возвращает календарную неделю даты (1-53). |
| YEAR() | Возвращает год. |
| YEARWEEK() | Возвращает год и неделю. |
Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
Внесение значений даты и времени в столбцы таблицы
Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:
Точно так же можно заказать товар с датой доставки через два месяца:
Извлечение данных по дате и времени
В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:
Заключение
Функции для работы с датами и временем
Поддержка часовых поясов
timeZone
Возвращает часовой пояс сервера.
Если функция вызывается в контексте распределенной таблицы, то она генерирует обычный столбец со значениями, актуальными для каждого шарда. Иначе возвращается константа.
Синтаксис
Возвращаемое значение
toTimeZone
Синтаксис
Аргументы
Возвращаемое значение
Пример
timeZoneOf
Возвращает название часового пояса для значений типа DateTime и DateTime64.
Синтаксис
Аргументы
Возвращаемое значение
Пример
timeZoneOffset
Возвращает смещение часового пояса в секундах от UTC. Функция учитывает летнее время и исторические изменения часовых поясов, которые действовали на указанную дату.
Для вычисления смещения используется информация из базы данных IANA.
Синтаксис
Аргументы
Возвращаемое значение
Пример
toYear
Переводит дату или дату-с-временем в число типа UInt16, содержащее номер года (AD).
toQuarter
Переводит дату или дату-с-временем в число типа UInt8, содержащее номер квартала.
toMonth
Переводит дату или дату-с-временем в число типа UInt8, содержащее номер месяца (1-12).
toDayOfYear
Переводит дату или дату-с-временем в число типа UInt16, содержащее номер дня года (1-366).
toDayOfMonth
Переводит дату или дату-с-временем в число типа UInt8, содержащее номер дня в месяце (1-31).
toDayOfWeek
toHour
toMinute
Переводит дату-с-временем в число типа UInt8, содержащее номер минуты в часе (0-59).
toSecond
Переводит дату-с-временем в число типа UInt8, содержащее номер секунды в минуте (0-59).
Секунды координации не учитываются.
toUnixTimestamp
Синтаксис
Возвращаемое значение
Пример
toStartOfYear
Округляет дату или дату-с-временем вниз до первого дня года.
Возвращается дата.
toStartOfISOYear
toStartOfQuarter
toStartOfMonth
Округляет дату или дату-с-временем вниз до первого дня месяца.
Возвращается дата.
Возвращаемое значение для некорректных дат зависит от реализации. ClickHouse может вернуть нулевую дату, выбросить исключение, или выполнить «естественное» перетекание дат между месяцами.
toMonday
Округляет дату или дату-с-временем вниз до ближайшего понедельника.
Возвращается дата.
toStartOfWeek(t[,mode])
Округляет дату или дату со временем до ближайшего воскресенья или понедельника в соответствии с mode.
Возвращается дата.
Аргумент mode работает точно так же, как аргумент mode toWeek(). Если аргумент mode опущен, то используется режим 0.
toStartOfDay
Округляет дату-с-временем вниз до начала дня. Возвращается дата-с-временем.
toStartOfHour
Округляет дату-с-временем вниз до начала часа.
toStartOfMinute
Округляет дату-с-временем вниз до начала минуты.
toStartOfSecond
Отсекает доли секунды.
Синтаксис
Аргументы
Возвращаемое значение
Примеры
Пример без часового пояса:
Пример с часовым поясом:
Смотрите также
toStartOfFiveMinute
Округляет дату-с-временем вниз до начала пятиминутного интервала.
toStartOfTenMinutes
Округляет дату-с-временем вниз до начала десятиминутного интервала.
toStartOfFifteenMinutes
Округляет дату-с-временем вниз до начала пятнадцатиминутного интервала.
toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])
toTime
Переводит дату-с-временем на некоторую фиксированную дату, сохраняя при этом время.
toRelativeYearNum
Переводит дату-с-временем или дату в номер года, начиная с некоторого фиксированного момента в прошлом.
toRelativeQuarterNum
Переводит дату-с-временем или дату в номер квартала, начиная с некоторого фиксированного момента в прошлом.
toRelativeMonthNum
Переводит дату-с-временем или дату в номер месяца, начиная с некоторого фиксированного момента в прошлом.
toRelativeWeekNum
Переводит дату-с-временем или дату в номер недели, начиная с некоторого фиксированного момента в прошлом.
toRelativeDayNum
Переводит дату-с-временем или дату в номер дня, начиная с некоторого фиксированного момента в прошлом.
toRelativeHourNum
Переводит дату-с-временем в номер часа, начиная с некоторого фиксированного момента в прошлом.
toRelativeMinuteNum
Переводит дату-с-временем в номер минуты, начиная с некоторого фиксированного момента в прошлом.
toRelativeSecondNum
Переводит дату-с-временем в номер секунды, начиная с некоторого фиксированного момента в прошлом.
toISOYear
Переводит дату-с-временем или дату в число типа UInt16, содержащее номер ISO года. ISO год отличается от обычного года, потому что в соответствии с ISO 8601:1988 ISO год начинается необязательно первого января.
Пример
toISOWeek
Пример
toWeek(date[, mode][, timezone])
Переводит дату-с-временем или дату в число UInt8, содержащее номер недели. Второй аргументам mode задает режим, начинается ли неделя с воскресенья или с понедельника и должно ли возвращаемое значение находиться в диапазоне от 0 до 53 или от 1 до 53. Если аргумент mode опущен, то используется режим 0.
Описание режимов (mode):
| Mode | Первый день недели | Диапазон | Неделя 1 это первая неделя … |
|---|---|---|---|
| 0 | Воскресенье | 0-53 | с воскресеньем в этом году |
| 1 | Понедельник | 0-53 | с 4-мя или более днями в этом году |
| 2 | Воскресенье | 1-53 | с воскресеньем в этом году |
| 3 | Понедельник | 1-53 | с 4-мя или более днями в этом году |
| 4 | Воскресенье | 0-53 | с 4-мя или более днями в этом году |
| 5 | Понедельник | 0-53 | с понедельником в этом году |
| 6 | Воскресенье | 1-53 | с 4-мя или более днями в этом году |
| 7 | Понедельник | 1-53 | с понедельником в этом году |
| 8 | Воскресенье | 1-53 | содержащая 1 января |
| 9 | Понедельник | 1-53 | содержащая 1 января |
Для режимов со значением «с 4 или более днями в этом году» недели нумеруются в соответствии с ISO 8601:1988:
Если неделя, содержащая 1 января, имеет 4 или более дней в новом году, это неделя 1.
Для режимов со значением «содержит 1 января», неделя 1 – это неделя содержащая 1 января. Не имеет значения, сколько дней в новом году содержала неделя, даже если она содержала только один день.
Пример
toYearWeek(date[,mode])
Возвращает год и неделю для даты. Год в результате может отличаться от года в аргументе даты для первой и последней недели года.
Аргумент mode работает точно так же, как аргумент mode toWeek(). Если mode не задан, используется режим 0.
Пример
date_trunc
Отсекает от даты и времени части, меньшие чем указанная часть.
Синтаксис
Аргументы
unit — единица измерения времени, в которой задана отсекаемая часть. String Literal.
Возможные значения:
value — дата и время. DateTime или DateTime64.
Возвращаемое значение
Примеры
Запрос без указания часового пояса:
Запрос с указанием часового пояса:
Смотрите также
date_add
Добавляет интервал времени или даты к указанной дате или дате со временем.
Синтаксис
Аргументы
unit — единица измерения времени, в которой задан интервал для добавления. String.
Возможные значения:
value — значение интервала для добавления. Int.
Возвращаемое значение
Пример
date_diff
Вычисляет разницу между двумя значениями дат или дат со временем.
Синтаксис
Аргументы
unit — единица измерения времени, в которой будет выражено возвращаемое значение функции. String.
Возможные значения:
Возвращаемое значение
Пример
date_sub
Вычитает интервал времени или даты из указанной даты или даты со временем.
Синтаксис
Аргументы
unit — единица измерения времени, в которой задан интервал для вычитания. String.
Возможные значения:
value — значение интервала для вычитания. Int.
Возвращаемое значение
Пример
timestamp_add
Добавляет интервал времени к указанной дате или дате со временем.
Синтаксис
Аргументы
unit — единица измерения времени, в которой задан интервал для добавления. String.
Возможные значения:
Возвращаемое значение
Пример
timestamp_sub
Вычитает интервал времени из указанной даты или даты со временем.
Синтакис
Аргументы
unit — единица измерения времени, в которой задан интервал для вычитания. String.
Возможные значения:
value — значение интервала для вычитания. Int.
Возвращаемое значение
Пример
Возвращает текущую дату и время.
Синтаксис
Параметры
Возвращаемое значение
Пример
Запрос без указания часового пояса:
Запрос с указанием часового пояса:
today
Возвращает текущую дату на момент выполнения запроса. Функция не требует аргументов.
То же самое, что toDate(now())
yesterday
timeSlot
timeSlots(StartTime, Duration,[, Size])
toYYYYMM
Переводит дату или дату со временем в число типа UInt32, содержащее номер года и месяца (YYYY * 100 + MM).
toYYYYMMDD
Переводит дату или дату со временем в число типа UInt32, содержащее номер года, месяца и дня (YYYY * 10000 + MM * 100 + DD).
toYYYYMMDDhhmmss
Переводит дату или дату со временем в число типа UInt64 содержащее номер года, месяца, дня и время (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss).
formatDateTime
Функция преобразует дату-и-время в строку по заданному шаблону. Важно: шаблон — константное выражение, поэтому использовать разные шаблоны в одной колонке не получится.
Синтаксис
Возвращаемое значение
Возвращает значение времени и даты в определенном вами формате.
Поля подстановки
Пример
dateName
Возвращает указанную часть даты.
Синтаксис
Аргументы
Возвращаемое значение
Пример
FROM_UNIXTIME
Функция преобразует Unix timestamp в календарную дату и время.
Примеры
Если указан только один аргумент типа Integer, то функция действует так же, как toDateTime, и возвращает тип DateTime.
В случае, когда есть два аргумента: первый типа Integer или DateTime, а второй является строкой постоянного формата — функция работает также, как formatDateTime, и возвращает значение типа String.