Функции Transact-SQL. SQL — Функции даты Sql день из даты
SQL работа с датами – настолько важна, что без знания основных sql операторов ни в одном стоящем проекте нельзя обойтись. Как ни крути, но во всех сервисах существует надобность работы со временем. Как правило, это вычисление периодов с одной по другую дату, например вывод списка зарегистрировавшихся пользователей за год, месяц, день, час.Я хочу привести ряд решений на часто встречающиеся задачи по работе с датами в SQL, с которыми сам ежедневно сталкивался, надеюсь, это будет актуально и полезно для вас.
Как получить текущую дату в SQL
WHERE date = CURDATE()
или другой вариант
WHERE date = STR_TO_DATE(now(), "%Y-%m-%d")
Прибавить к дате один час в SQL
DATE_ADD("2013-03-30", INTERVAL 1 HOUR)
Прибавить к дате один день в SQL
DATE_ADD("2013-03-30", INTERVAL 1 DAY)
Аналогично можно прибавлять любое количество дней к текущей дате.
Прибавить к дате один месяц в SQL
DATE_ADD("2013-03-30", INTERVAL 1 MONTH)
Аналогично можно прибавлять любое количество месяцев к текущей дате.
Получить вчерашний день в SQL
DATE_ADD(CURDATE(), INTERVAL -1 DAY)
или
DATE_SUB(CURDATE(), INTERVAL 1 DAY)
Получить дату начала текущей недели в SQL
эта одна из самых сложных на первый взгляд задач, но решается очень просто
CURDATE()-WEEKDAY(CURDATE());
Получить выборку с этого понедельника по текущий день недели в SQL
Получить выборку с первого числа текущего месяца по текущий день недели в SQL
WHERE (date BETWEEN
(CURDATE()-WEEKDAY(CURDATE()))
AND
CURDATE())
Как получить дату рождения пользователя в SQL
Найти всех пользователей у которых день рождение в следующем месяце в SQL
Кроме вышеприведенных кейсов по работе с датами в SQL, рекомендую ознакомиться с документацией по следующим операторам: Работа с датами в SQl, как оказывается не такая сложная, и теперь вместо того чтобы вычислять периоды средствами PHP можно делать это еще на этапе выполнения SQL запроса и получать необходимую выборку данных.
Приветствую Вас, уважаемые читатели блога сайт. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке SQL
есть множество функций связанных с датой и временем, сегодня их и рассмотрим. Все ниже рассмотренные функции работают с календарными типами данных. Чтобы получить текущую дату и время
используется функция NOW ()
. SELECT NOW () Для получения только текущей даты
есть функция CURDATE ()
. SELECT CURDATE () И функция CURTIME ()
, которая возвращает только текущее время
: SELECT CURTIME () Функции 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) SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL "3 1:20" DAY_MINUTE) Функция SUBDATE (date, INTERVAL value)
производит вычитание
значения value из даты
date . Пример: SELECT SUBDATE ("2015-09-28 10:30:20", INTERVAL 20 HOUR) Функция PERIOD_ADD (period, n)
прибавляет
к значению period n месяцев
. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример: SELECT PERIOD_ADD (201509, 4) Функция TIMESTAMPADD (interval, n, date)
прибавляет к дате
date временной интервал
n , значения которого задаются параметром interval . Возможные значения параметра interval: SELECT TIMESTAMPADD (QUARTER, 1, "2015-09-28") Функция SUBTIME (date, time)
вычитает из даты
date время
time. Пример: SELECT SUBTIME ("2015-09-28 10:30:20", "50:20:19") Функция TIMEDIFF (date1, date2)
вычисляет разницу в часах, минутах и секундах
между двумя датами date1 и date2 . Пример: SELECT TIMEDIFF ("2015-09-28 10:30:20", "2015-09-29 10:30:20") Функция DATEDIFF (date1, date2)
вычисляет разницу в днях
между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример: SELECT DATEDIFF ("2015-09-28 00:00:20", "2015-09-27 23:40:20") С помощью этой функции легко определить сколько дней прошло с даты публикации статьи: SELECT DATEDIFF (CURDATE (), date_publication) FROM posts WHERE id_post = 1 Функция PERIOD_DIFF (period1, period2)
вычисляет разницу в месяцах
между двумя датами. Даты должны быть представлены в формате YYYYMM . Например, узнаем сколько месяцев прошло с января 2015 по сентябрь 2015: SELECT PERIOD_DIFF (201509, 201501) Функция TIMESTAMPDIFF (interval, date1, date2)
вычисляет разницу между датами date2 и date1 в единицах указанных в параметре interval . При этом interval может принимать следующие значения: SELECT TIMESTAMPDIFF (HOUR, "2015-09-28 10:30:20", "2015-09-28 19:50:20") Функция DATE (datetime)
возвращает дату, отсекая время
. Пример: SELECT DATE ("2015-09-28 10:30:20") Функция TIME (datetime)
возвращает время, отсекая дату
. Пример: SELECT TIME ("2015-09-28 10:30:20") Функция TIMESTAMP (date)
возвращает полный формат со временем
даты date . Пример: TIMESTAMP ("2015-09-28") DAY (date)
и DAYOFMONTH (date)
. Функции-синонимы, которые возвращают порядковый номер дня месяца
. Пример: SELECT DAY ("2015-09-28"), DAYOFMONTH ("2015-09-28") Функции DAYNAME (date)
, DAYOFWEEK (date)
и WEEKDAY (date)
. Первая функция возвращает название дня недели
, вторая — номер дня недели
(отсчет от 1 — воскресенье до 7 — суббота), третья также номер дня недели только другой отсчет(отсчет от 0 — понедельник, до 6 — воскресенье). Пример: SELECT DAYNAME ("2015-09-28"), DAYOFWEEK ("2015-09-28"), WEEKDAY ("2015-09-28") Функции WEEK (date)
и WEEKOFYEAR (datetime)
. Обе функции возвращают номер недели в году
, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример: SELECT WEEK ("2015-09-28 10:30:20"), WEEKOFYEAR ("2015-09-28 10:30:20") Функция MONTH (date)
возвращает числовое значение месяца
(от 1 до 12), а MONTHNAME (date)
название месяца
. Пример: SELECT MONTH ("2015-09-28 10:30:20"), MONTHNAME ("2015-09-28 10:30:20") Функция QUARTER (date)
возвращает номер квартала
года (от 1 до 4). Пример: SELECT QUARTER ("2015-09-28 10:30:20") Функция YEAR (date)
возвращает значение года
(от 1000 до 9999). Пример: SELECT YEAR ("2015-09-28 10:30:20") Функция DAYOFYEAR (date)
возвращает порядковый номер дня
в году (от 1 до 366). Прмиер: SELECT DAYOFYEAR ("2015-09-28 10:30:20") Функция HOUR (datetime)
возвращает значение часа
(от 0 до 23). Пример: SELECT HOUR ("2015-09-28 10:30:20") Функция MINUTE (datetime)
возвращает значение минут
(от 0 до 59). Пример: SELECT MINUTE ("2015-09-28 10:30:20") Функция SECOND (datetime)
возвращает значение секунд
(от 0 до 59). Пример: SELECT SECOND ("2015-09-28 10:30: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") Взаимообратные функции TO_DAYS (date)
и FROM_DAYS (n)
. Первая преобразует дату в количество дней
, прошедших с нулевого года. Вторая, наоборот, принимает число дней
, прошедших с нулевого года и преобразует их в дату
. Пример: SELECT TO_DAYS ("2015-09-28 10:30:20"), FROM_DAYS (736234) Взаимообратные функции UNIX_TIMESTAMP (date)
и FROM_UNIXTIME (n)
. Первая преобразует дату в количество секунд
, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд
, с 1 января 1970 года и преобразует их в дату
. Пример: SELECT UNIX_TIMESTAMP ("2015-09-28 10:30:20"), FROM_UNIXTIME (1443425420) Взаимообратные функции TIME_TO_SEC (time)
и SEC_TO_TIME (n)
. Первая преобразует время в количество секунд
, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример: SELECT TIME_TO_SEC ("10:30:20"), SEC_TO_TIME (37820) Функция MAKEDATE (year, n)
принимает год year и номер дня в году n и преобразует их в дату. Пример. Получение текущих даты и времени Для получения текущей даты (без времени) в формате YYYY-MM-DD можно использовать функции: Для получения текущего времени (без даты) в формате HH:MM:SS можно использовать функции: Для получения текущих даты и времени в формате YYYY-MM-DD HH:MM:SS можно использовать любую из следующих функций: Пример 1
SELECT CURDATE(); или SELECT CURRENT_DATE(); Результат: 2016-08-28
SELECT CURTIME(); или SELECT CURENT_TIME(); Результат: 19:29:54
SELECT NOW(); или SELECT SYSDATE(); или SELECT CURRENT_TIMESTAMP; Результат: 2016-08-28 19:29:54
С датами и временем часто приходится проводить арифметические операции: прибавлять к заданной дате определенный временной интервал или вычитать. Прибавление временного интервала можно осуществить тремя способами: Вычитание временного интервала также можно осуществить тремя способами: Исходная_дата
– это дата, к которой прибавляется или вычитается определенный интервал времени.
Выражение
– это сам прибавляемый или вычитаемый интервал времени, заданный в текстовом формате.
Тип
– аргумент, указывающий тип прибавляемого интервала. Этот параметр уточняет, как правильно интерпретировать выражение. Например, выражение ‘3:24’ можно интерпретировать и как 3 часа 24 минуты, и как 3 минуты 24 секунды. Если указан тип «MINUTE_SECOND», то интерпретация будет однозначной.
Связь между аргументами выражение и тип показана в таблице: Пример 2
Следующие три команды делают одну и ту же операцию. Они прибавляют секунду к заданному времени. SELECT "2016-09-10 23:59:59" + Interval 1 SECOND; SELECT ADDDATE("2016-09-10 23:59:59", Interval 1 SECOND); SELECT DATE_ADD("2016-09-10 23:59:59", Interval 1 SECOND); Результат всех трех команд одинаковый: 2016-09-11 00:00:00.
Пример 3
Следующие три команды вычисляют дату предшествующую заданной ровно на полтора года: SELECT DATE_SUB("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH); SELECT SUBDATE("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH); SELECT "2016-09-10 23:59:59" - Interval "1-6" YEAR_MONTH; Результат всех трех команд одинаковый: 2015-03-10 23:59:59.
Иногда бывает удобно работать с датой, представленной в виде количества дней прошедших с года 0. Для перевода даты в такой формат и обратно в MySQL существует две функции: SELECT CURDATE(), CURTIME(), NOW(); INSERT INTO incoming (id_vendor, date_incoming) VALUES
("2", curdate()); SELECT id_vendor, date_incoming FROM incoming; SELECT ADDDATE("2011-04-15 00:02:00", INTERVAL "02:45" HOUR_MINUTE); SELECT SUBDATE("2011-04-15 00:02:00", INTERVAL "23:53" HOUR_MINUTE); SELECT PERIOD_ADD(201102, 2); FRAC_SECOND - микросекунды SELECT TIMESTAMPADD(DAY, 2, "2011-04-02"); SELECT TIMEDIFF("2011-04-17 23:50:00", "2011_04-16 14:50:00"); SELECT PERIOD_DIFF(201108, 201001); FRAC_SECOND - микросекунды 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; SELECT SUBTIME("2011-04-18 23:17:00", "02:15:30"); SELECT DATE("2011-04-15 00:03:20"); SELECT TIME("2011-04-15 00:03:20"); SELECT TIMESTAMP("2011-04-17"); SELECT DAY("2011-04-17"), DAYOFMONTH("2011-04-17"); SELECT DAYNAME("2011-04-17"), DAYOFWEEK("2011-04-17"), WEEKDAY("2011-04-17"); SELECT WEEK("2011-04-17"), WEEKOFYEAR("2011-04-17 23:40:00"); SELECT MONTH("2011-04-17"), MONTHNAME("2011-04-17"); SELECT QUARTER("2011-04-17"); SELECT YEAR("2011-04-17"); SELECT DAYOFYEAR("2011-04-17"); SELECT HOUR("2011-04-17 18:20:03"); SELECT TIME_TO_SEC("22:10:30"), SEC_TO_TIME(45368); SELECT MAKEDATE(2011, 120); Последнее обновление: 29.07.2017 T-SQL предоставляет ряд функций для работы с датами и временем: GETDATE
: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime SELECT GETDATE() -- 2017-07-28 21:34:55.830 GETUTCDATE
: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830 SYSDATETIME
: возвращает текущую локальную дату и время на основе системных часов, но отличие от GETDATE состоит в том, что
дата и время возвращаются в виде объекта datetime2 SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744 SYSUTCDATETIME
: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта
datetime2 SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777 SYSDATETIMEOFFSET
: возвращает объект datetimeoffset(7), который содержит дату и время относительно
GMT SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00 DAY
: возвращает день даты, который передается в качестве параметра SELECT DAY(GETDATE()) -- 28 MONTH
: возвращает месяц даты SELECT MONTH(GETDATE()) -- 7 YEAR
: возвращает год из даты SELECT YEAR(GETDATE()) -- 2017 DATENAME
: возвращает часть даты в виде строки. Параметр выбора части даты передается в качестве первого параметра, а сама дата передается в
качестве второго параметра: SELECT DATENAME(month, GETDATE()) -- July
Для определения части даты можно использовать следующие параметры (в скобках указаны их сокращенные версии): year (yy, yyyy) : год quarter (qq, q) : квартал month (mm, m) : месяц dayofyear (dy, y) : день года day (dd, d) : день месяца week (wk, ww) : неделя weekday (dw) : день недели hour (hh) : час minute (mi, n) : минута second (ss, s) : секунда millisecond (ms) : миллисекунда microsecond (mcs) : микросекунда nanosecond (ns) : наносекунда tzoffset (tz) : смешение в минутах относительно гринвича (для объекта datetimeoffset) DATEPART
: возвращает часть даты в виде числа. Параметр выбора части даты передается в качестве первого параметра (используются те же параметры, что и для
DATENAME), а сама дата передается в качестве второго параметра: SELECT DATEPART(month, GETDATE()) -- 7 DATEADD
: возвращает дату, которая является результатом сложения числа к определенному компоненту даты. Первый параметр представляет компонент даты, описанный выше для функции DATENAME. Второй параметр - добавляемое количество. Третий параметр - сама дата, к которой надо сделать прибавление: SELECT DATEADD(month, 2, "2017-7-28") -- 2017-09-28 00:00:00.000
SELECT DATEADD(day, 5, "2017-7-28") -- 2017-08-02 00:00:00.000
SELECT DATEADD(day, -5, "2017-7-28") -- 2017-07-23 00:00:00.000
Если добавляемое количество представляет отрицательное число, то фактически происходит уменьшение даты. DATEDIFF
: возвращает разницу между двумя датами. Первый параметр - компонент даты, который указывает, в каких единицах стоит измерять
разницу. Второй и третий параметры - сравниваемые даты: SELECT DATEDIFF(year, "2017-7-28", "2018-9-28") -- разница 1 год
SELECT DATEDIFF(month, "2017-7-28", "2018-9-28") -- разница 14 месяцев
SELECT DATEDIFF(day, "2017-7-28", "2018-9-28") -- разница 427 дней TODATETIMEOFFSET
: возвращает значение datetimeoffset, которое является результатом сложения временного
смещения с другим объектом datetimeoffset SELECT TODATETIMEOFFSET("2017-7-28 01:10:22", "+03:00") SWITCHOFFSET
: возвращает значение datetimeoffset, которое является результатом сложения временного
смещения с объектом datetime2 SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "+02:30") EOMONTH
: возвращает дату последнего дня для месяца, который используется в переданной в качестве параметра дате. SELECT EOMONTH("2017-02-05") -- 2017-02-28
SELECT EOMONTH("2017-02-05", 3) -- 2017-05-31
В качестве необязательного второго параметра можно передавать количество месяцев, которые необходимо прибавить к дате. Тогда последний день месяца будет вычисляться для новой даты. DATEFROMPARTS
: по году, месяцу и дню создает дату SELECT DATEFROMPARTS(2017, 7, 28) -- 2017-07-28 ISDATE
: проверяет, является ли выражение датой. Если является, то возвращает 1, иначе возвращает 0. SELECT ISDATE("2017-07-28") -- 1
SELECT ISDATE("2017-28-07") -- 0
SELECT ISDATE("28-07-2017") -- 0
SELECT ISDATE("SQL") -- 0 В качестве примера использования функций можно привести создание таблицы заказов, которая содержит дату заказа: CREATE TABLE Orders
(Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL DEFAULT GETDATE(),
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL);
Выражение DEFAULT GETDATE() указывает, что если при добавлении данных не передается дата, то она автоматически вычисляется с помощью функции GETDATE(). Другой пример - найдем заказы, которые были сделаны 16 дней назад: SELECT * FROM Orders
WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16
SELECT name, birth, CURRENT_DATE,
(YEAR(CURRENT_DATE)-YEAR(birth))
- (RIGHT(CURRENT_DATE,5)
SELECT name, birth FROM user
WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
или другой вариант
SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;
NOW()
– Возвращает текущую дату и время.
CURDATE()
– Возвращает текущую дату.
CURTIME()
– Возвращаем текущее время.
DATE()
– Состоит из двух частей даты и времени.
EXTRACT()
– Возвращает одно значения даты/времени.
DATE_ADD()
– Добавляет до выборки указанное число дней/мину/часов и т.д.
DATE_SUB()
– Вычитываем указанный интервал от даты.
DATEDIFF()
– Возвращает значение времени между двумя датами.
DATE_FORMAT()
– Функция для различного вывода информации о времени.Получение текущей даты и времени.
Результат: 2015-09-25 14:42:53
Результат: 2015-09-25
Результат: 14:42:53Прибавление и вычитание дат и времени
Результат: 2015-09-29 10:30:20
Результат: 2015-10-01 11:50:20
Результат: 2015-09-27 14:30:20
Результат: 201601
Результат: 2015-12-28
Результат: 2015-09-26 08:10:01Вычисление интервала между датами
Результат: -24:10:00
Результат: 1
Результат: 9
Результат: 9Получение различных форматов даты и времени и другой информации
Результат: 2015-09-28
Результат: 10:30:20
Результат: 2015-09-28 00:00:00
Результат: 28 | 28
Результат: Monday 2 | 0
Результат: 39 | 40
Результат: 9 | September
Результат: 3
Результат: 2015
Результат: 271
Результат: 10
Результат: 30
Результат: 20
Результат: 2015 | 9 | 28 | 10 | 30 | 20
Результат: 736234 | 2015-09-28
Результат: 1443425420 | 2015-09-28 10:30:20
Результат: 37820 | 10:30:20 Прибавление и вычитание временного интервала
Функции перевода в другие единицы
SQL - Урок 13. Функции даты и времени
Эти функции предназначены для работы с календарными типами данных. Рассмотрим наиболее применимые.
Ну вот, на сегодня все. В следующий раз рассмотрим функции, которые помогают переводить даты из одного формата
в другой.
Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее время. В нашем магазине
все поставки и продажи используют текущее время. Поэтому для добавления записей о поставах, и продажах удобно использовать функцию
CURDATE(). Например, пусть в наш магазин пришел товар, давайте добавим информацию об этом в таблицу Поставка (incoming):
Если бы мы хранили дату поставки с типом datatime, то нам больше подошла бы функция NOW().
Предположим, мы ошиблись при вводе даты для первого поставщика, давайте уменьшим его дату на одни сутки:
В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR).
Давайте для пример уменьшим дату поставки для второго поставщика на 1 неделю:
В нашей таблице Поставки (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 минут:
SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
WEEK - недели
MONTH - месяцы
QUARTER - кварталы
YEAR - годы
SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
WEEK - недели
MONTH - месяцы
QUARTER - кварталы
YEAR - годы