Функции работы с датой sql. Функции Transact-SQL
Последнее обновление: 29.07.2017
T-SQL предоставляет ряд функций для работы с датами и временем:
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)
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
Для определения части даты можно использовать следующие параметры (в скобках указаны их сокращенные версии):
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
Приветствую Вас, уважаемые читатели блога сайт. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке 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 и преобразует их в дату. Пример.
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-92 специфицирует только функции, возвращающие системную дату/время.
Например, функция CURRENT_TIMESTAMP возвращает сразу и дату, и время. Плюс имеются функции
возвращающие что-либо одно.
DATEADD (datepart
, number
, date
) Эта функция возвращает значение типа datetime
, которое получается добавлением к дате date
количества интервалов типа datepart
, равного number
.
Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д.
Допустимые значения аргумента datepart
приведены ниже и взяты из BOL. DATEDIFF (datepart
, startdate
, enddate
) Функция возвращает интервал времени, прошедшего между двумя временными отметками - startdate
(начальная отметка) и enddate
(конечная отметка). Этот интервал может быть измерен в разных
единицах. Возможные варианты определяются аргументом datepart
и перечислены выше применительно
к функции DATEADD
.
DATEPART (datepart
, date
) Эта функция возвращает целое число, представляющее собой указанную аргументом datepart
часть заданной вторым аргументом даты (date
).
Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, вычесть из первого второе, чтобы получить продолжительность рейса. В противном
случае к разности нужно добавить одни сутки (24*60 = 1440 минут). DATENAME (datepart
, date
) Эта функция возвращает символьное представление составляющей (datepart
) указанной даты (date
).
Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в вышеприведенной таблице.
@@DATEFIRST возвращает число, которое определяет первый день недели, установленный для текущей сессии. При этом 1 соответствует понедельнику, а 7, соответственно, воскресенью. Т.е. если
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()
– Функция для различного вывода информации о времени.
Естественно, в силу такой ограниченности, реализации языка расширяют стандарт за счет добавления функций, облегчающий работу пользователей с данными этого типа.
Здесь мы рассмотрим функции обработки даты/времени в T-SQL.Функция DATEADD
Синтаксис
Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать
потому, что дробная часть значения аргумента datepart
отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день.
Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP
функцию T-SQL GETDATE()
с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.
Пример
(схема 4). Определить, какой будет день через неделю после последнего полета.
Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime
.
SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))
Функция DATEDIFF
Синтаксис
Пример
(схема 4). Определить количество дней, прошедших между первым и последним совершенными рейсами.
Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным.
Во-вторых, ненадежно делать какие либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime
.
Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место.
Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло?
Здесь может помочь функция T-SQL DATEPART
.
Функция DATEPART
Синтаксис
Список допустимых значений аргумента datepart
, описанный выше в данном разделе, дополняется еще одним значением
Заметим, что возвращаемое функцией DATEPART
значение в этом случае (номер дня недели) зависит
от настроек, которые можно изменить с помощью оператора SET DATEFIRST
, устанавливающего первый день
недели. Для кого-то понедельник - день тяжелый, а для кого-то - воскресенье. Кстати, последнее значение принимается по умолчанию.
Однако вернемся к нашему примеру. В предположении, что время вылета/прилета является кратным минуте,
мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными
значениями, приведем результат к наименьшему интервалу - минутам. Итак, время вылета рейса 1123 в минутах
Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.
SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM
(SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep,
DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr
FROM trip WHERE trip_no=1123
) tm
Пример
(4 схема). Определить дату и время вылета рейса 1123.
В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время,
т.к. в соответствии с предметной областью каждый рейс может выполняться только один раз в день.
Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip
DISTINCT
необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса
дублируются в этой таблице для каждого пассажира данного рейса.
Функция DATENAME
Синтаксис
Это дает нам простую возможность конкатенировать компоненты даты, получая любой нужный формат представления. Например, конструкция
даст нам 31, а
SELECT DATENAME (day , "2003-12-31")
- 365.
SELECT DATENAME (dayofyear , "2003-12-31")
В ряде случаев функцию DATEPART
можно заменить более простыми функциями. Вот они:
DAY
(date
) - целочисленное представление дня указанной даты.
Эта функция эквивалентна функции DATEPART
(dd
, date
).
MONTH
(date
) - целочисленное представление месяца указанной даты.
Эта функция эквивалентна функции DATEPART
(mm
, date
).
YEAR
(date
) - целочисленное представление года указанной даты.
Эта функция эквивалентна функции DATEPART
(yy
, date
).
Функция @@DATEFIRST
возвращает 7, то первым днем недели считается воскресенье (соответствует текущим настройкам на сайте).
SELECT @@DATEFIRST;