SQL — Функции даты. Функции Transact-SQL Sql функции работы с датами
Получение текущих даты и времени
Для получения текущей даты (без времени) в формате YYYY-MM-DD можно использовать функции:
- CURDATE()
- CURRENT_DATE()
Для получения текущего времени (без даты) в формате HH:MM:SS можно использовать функции:
- CURTIME()
- CURRENT_TIME()
Для получения текущих даты и времени в формате YYYY-MM-DD HH:MM:SS можно использовать любую из следующих функций:
- NOW()
- SYSDATE()
- CURRENT_TIMESTAMP
Пример 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
Прибавление и вычитание временного интервала
С датами и временем часто приходится проводить арифметические операции: прибавлять к заданной дате определенный временной интервал или вычитать. Прибавление временного интервала можно осуществить тремя способами:
- Функция DATE_ADD(исходная_дата, INTERVAL выражение тип) ;
- Функция ADDDATE (исходная_дата, INTERVAL выражение тип );
- Выражение: исходная_дата + INTERVAL выражение тип.
Вычитание временного интервала также можно осуществить тремя способами:
- Функция DATE_SUB(исходная_дата, INTERVAL выражение тип);
- Функция SUBDATE (исходная_дата, INTERVAL выражение тип);
- Выражение: исходная_дата - INTERVAL выражение тип.
Исходная_дата – это дата, к которой прибавляется или вычитается определенный интервал времени. Выражение – это сам прибавляемый или вычитаемый интервал времени, заданный в текстовом формате. Тип – аргумент, указывающий тип прибавляемого интервала. Этот параметр уточняет, как правильно интерпретировать выражение. Например, выражение ‘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 существует две функции:
- TO_DAYS(дата ) – возвращает номер дня соответствующего аргументу дата.
- FROM_DAYS(номер_дня) – возвращает дату в соответствии с номером дня.
От автора: сегодня мы поговорим о том, как работают в SQL функции даты. В следующей таблице приведен список всех важных функций, связанных с датой и временем, которые доступны. Существуют и другие, поддерживаемые различными СУБД. Данный список представляет функции, доступные в СУБД MySQL.
ADDDATE(). Добавляет даты
ADDTIME(). Добавляет время
CONVERT_TZ(). Преобразует из одного часового пояса в другой
CURDATE(). Возвращает текущую дату
CURRENT_DATE(), CURRENT_DATE. Синонимы для CURDATE()
CURRENT_TIME(), CURRENT_TIME. Синонимы для CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP. Синонимы для NOW()
CURTIME(). Возвращает текущее время
DATE_ADD(). Слагает две даты
DATE_FORMAT(). Задает указанный формат даты
DATE_SUB(). Вычитает одну дату из другой
DATE(). Извлекает часть, относящуюся к дате, из выражения представляющего дату или время и дату
DATEDIFF(). Вычитает одну дату из другой
DAY(). Синоним для DAYOFMONTH()
DAYNAME(). Возвращает день недели
DAYOFMONTH(). Возвращает день месяца (1-31)
DAYOFWEEK(). Возвращает индекс дня недели аргумента
DAYOFYEAR(). Возвращает номер дня в году (1-366)
EXTRACT. Извлекает часть, относящуюся к дате
FROM_DAYS(). Преобразует номер дня в дату
FROM_UNIXTIME(). Форматирует дату как временную метку UNIX
HOUR(). Извлекает час
LAST_DAY. Возвращает последний день месяца для аргумента
LOCALTIME(), LOCALTIME. Синоним для NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP (). Синоним для NOW()
MAKEDATE(). Создает дату из года и дня года
MAKETIME. MAKETIME(). MICROSECOND(). Возвращает микросекунды из аргумента
MINUTE(). Возвращает минуты из аргумента
MONTH(). Возврат месяца из даты
MONTHNAME(). Возвращает название месяца
NOW(). Возвращает текущую дату и время
PERIOD_ADD(). Добавляет период к году-месяцу
PERIOD_DIFF(). Возвращает количество месяцев между периодами
QUARTER(). Возвращает квартал из аргумента
SEC_TO_TIME(). Преобразует секунды в формат «HH: MM: SS»
SECOND(). Возвращает секунды (0-59)
STR_TO_DATE(). Преобразует строку в дату
SUBDATE(). При вызове с тремя аргументами синоним DATE_SUB()
SUBTIME(). Вычитает время
SYSDATE(). Возвращает время выполнения функции
TIME_FORMAT(). Задает формат времени
TIME_TO_SEC(). Возвращает аргумент, преобразованный в секунды
TIME(). Извлекает часть, относящуюся ко времени, из переданного выражения
TimeDiff(). Вычитает время
TIMESTAMP(). С одним аргументом эта функция возвращает выражение даты или даты и времени. С двумя аргументами — слагает эти два аргумента
TIMESTAMPADD(). Добавляет интервал к выражению даты и времени
TIMESTAMPDIFF(). Вычитает интервал из выражения даты и времени
TO_DAYS(). Возвращает аргумент даты, преобразованный в дни
UNIX_TIMESTAMP(). Возвращает временную метку UNIX
UTC_DATE(). Возвращает текущую дату UTC
UTC_TIME(). Возвращает текущее время UTC
UTC_TIMESTAMP(). Возвращает текущую дату и время UTC
WEEK(). Возвращает номер недели
WEEKDAY(). Возвращает индекс дня недели
WEEKOFYEAR(). Возвращает календарный номер недели (1-53)
YEAR(). Возвращает год
YEARWEEK(). Возвращает год и неделю
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
При вызове со вторым аргументом, заданным в виде INTERVAL, функция ADDDATE() является синонимом DATE_ADD(). Связанная функция SUBDATE() является синонимом DATE_SUB(). Информацию об аргументе блока INTERVAL см. в разделе DATE_ADD().
При вызове со вторым аргументом, заданным в днях, MySQL рассматривает это как целое число дней для добавления в выражение.
ADDTIME(expr1,expr2)
ADDTIME () добавляет expr2 к expr1 и возвращает результат. Expr1 является выражением времени или даты и времени, в то время как expr2 является выражением времени.
CONVERT_TZ(dt,from_tz,to_tz)
Преобразует значение даты и времени dt из часового пояса, заданного в from_tz, в часовой пояс, заданный в to_tz, и возвращает полученное значение. Эта функция возвращает NULL, если аргументы недействительны.
CURDATE()
Возвращает текущую дату как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.
CURRENT_DATE и CURRENT_DATE()
CURRENT_DATE и CURRENT_DATE() являются синонимами для CURDATE()
CURTIME()
Возвращает текущее время как значение в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или в числовом контексте. Значение выражается для текущего часового пояса.
CURRENT_TIME и CURRENT_TIME()
CURRENT_TIME и CURRENT_TIME() являются синонимами для CURTIME().
CURRENT_TIMESTAMP и CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP и CURRENT_TIMESTAMP() являются синонимами для NOW().
DATE(expr)
Извлекает часть, относящуюся к дате, из выражения даты или даты и времени expr.
DATEDIFF(expr1,expr2)
DATEDIFF() возвращает expr1.expr2, выраженное как количество дней между двумя датами. И expr1, и expr2 являются выражениями даты или даты и времени. В расчетах используются только части относящиеся к дате.
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
Эти функции выполняют арифметические операции с датами. date представлено как значение DATETIME или DATE, указывающее начальную дату. expr представляет собой выражение, определяющее значение интервала, который нужно добавить или вычесть из исходной даты. expr — это строка; она может начинаться с «-» для отрицательных интервалов.
unit является ключевым словом, указывающим единицы измерения для выражения. Ключевое слово INTERVAL и обозначение единиц не чувствительны к регистру. В следующей таблице показана ожидаемая форма аргумента expr для каждого значения единицы измерения.
Значения QUARTER и WEEK доступны в MySQL начиная с версии 5.0.0.
DATE_FORMAT (date,format)
Эта команда задает формат значения date в соответствии с указанной строкой format. В строке format могут использоваться следующие указатели. Перед указателями формата необходимо добавлять символ ‘%’.
%a. Сокращенное название дня недели (Sun..Sat)
%b. Сокращенное название месяца (Jan..Dec)
%с. Числовое обозначение месяца (0…12)
%D. День месяца с английским суффиксом (0, 1, 2, 3,.)
%d. Числовое обозначение дня месяца (00..31)
%е. Числовое обозначение дня месяца (00..31)
%f. Микросекунды (000000..999999)
%H. Час (00..23)
%h. Час (01..12)
%I. Час (01..12)
%i. Числовое обозначение минут (00..59)
%J. День года (001..366)
%k. Час (0..23)
%l. Час (1..12)
%M. Название месяца (January..December)
%м. Числовое обозначение месяца (00..12)
%р. AM или PM
%r. Время, 12-часовой формат (чч: мм: сс, за которым следуют AM или PM)
%S. Секунды (00..59)
%s. Секунды (00..59)
%Т. Время, 24-часовой формат (чч: мм: сс)
%U. Неделя (00..53), где воскресенье — первый день недели
%u. Неделя (00..53), где понедельник — первый день недели
%V. Неделя (01..53), где воскресенье — первый день недели; используется вместе с %X
%v. Неделя (01..53), где понедельник — первый день недели; используется вместе с %x
%W. Название дня недели (Sunday..Saturday)
%w. День недели (0=Sunday..6=Saturday)
%X. Год для недели, где первый день недели — воскресенье, число из четырех цифр; используется вместе с %V
%x. Год для недели, где первый день недели — понедельник, число из четырех цифр; используется вместе с %V
%Y. Год, число, четыре цифры
%y. Числовое обозначение года (две цифры)
%%. Буквально символ %
%x. x, для всех.x., не перечисленных выше
DATE_SUB(date,INTERVAL expr unit)
Аналогично функции DATE_ADD ().
DAY(date)
DAY() является синонимом функции DAYOFMONTH().
DAYNAME(date)
Возвращает день недели для указанной даты.
DAYOFMONTH(date)
Возвращает день месяца для указанной даты в диапазоне от 0 до 31.
DAYOFWEEK(date)
Возвращает индекс дня недели (1 = Sunday, 2 = Monday, ., 7 = Saturday). Эти значения индекса соответствуют стандарту ODBC.
DAYOFYEAR(date)
Возвращает день года для указанной даты в диапазоне от 1 до 366.
EXTRACT(unit FROM date)
Функция EXTRACT() использует те же типы указателей единиц измерения, что и DATE_ADD() или DATE_SUB(), но не выполняет арифметические операции с датами, а извлекает из даты часть относящуюся к указателю единиц измерения.
FROM_DAYS(N)
Возвращается значение DATE с учетом числа дней N.
Примечание. Используйте FROM_DAYS() для старых дат осторожно. Функция не предназначена для работы со значениями дат до введения григорианского календаря (1582).
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
Возвращает представление аргумента unix_timestamp как значение в формате «YYYY-MM-DD HH: MM: SS или YYYYMMDDHHMMSS» в зависимости от того, используется ли эта функция в строковом или в числовом контексте. Значение выражается в текущем часовом поясе. Параметр unix_timestamp является внутренним значением метки времени, которое создается функцией UNIX_TIMESTAMP().
Если format указано, результат форматируется в соответствии со строкой format, которая используется так же, как описано в разделе DATE_FORMAT().
HOUR(time)
Возвращает часы из указанного времени. Диапазон возвращаемого значения составляет от 0 до 23. Однако диапазон значений TIME на самом деле намного больше, поэтому HOUR может возвращать значения, превышающие 23.
LAST_DAY(date)
Принимает значение даты или даты и времени и возвращает значение, соответствующее последнему дню месяца. Возвращает NULL, если аргумент недействителен.
LOCALTIME и LOCALTIME()
LOCALTIME и LOCALTIME() являются синонимами для NOW().
LOCALTIMESTAMP и LOCALTIMESTAMP()
LOCALTIMESTAMP и LOCALTIMESTAMP() являются синонимами для NOW().
MAKEDATE(year,dayofyear)
Возвращает значения даты, заданного года и дня года. Значение dayofyear должно быть больше 0 или результат будет NULL.
MAKETIME(hour,minute,second)
Возвращает значение времени, рассчитанное из аргументов hour, minute и second.
MICROSECOND(expr)
Возвращает микросекунды из выражения времени или выражения datetime(expr) в виде числа в диапазоне от 0 до 999999.
MINUTE(time)
Возвращает минуты для указанного времени, в диапазоне от 0 до 59.
MONTH(date)
Возвращает месяц для указанной даты в диапазоне от 0 до 12.
MONTHNAME(date)
Возвращает полное название месяца для указанной даты.
NOW()
Возвращает текущую дату и время как значение в формате «YYYY-MM-DD HH: MM: SS» или YYYYMMDDHHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте. Это значение выражается в текущем часовом поясе.
PERIOD_ADD(P,N)
Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает значение в формате YYYYMM. Обратите внимание, что аргумент периода P не является значением даты.
PERIOD_DIFF(P1,P2)
Возвращает количество месяцев между периодами P1 и P2. Периоды P1 и P2 должны указываться в формате YYMM или YYYYMM. Обратите внимание, что аргументы периодов P1 и P2 не являются значениями даты.
QUARTER(date)
Возвращает квартал года для указанной даты в диапазоне от 1 до 4.
SECOND(time)
Возвращает значение секунд для времени в диапазоне от 0 до 59.
SEC_TO_TIME(seconds)
Возвращает аргумент seconds, преобразованный в часы, минуты и секунды в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте.
STR_TO_DATE(str,format)
Это инверсивная функция к функции DATE_FORMAT(). Она принимает строку str и строку формата format. Функция STR_TO_DATE() возвращает значение DATETIME, если строка формата содержит как дату, так и время. В противном случае она возвращает значение DATE или TIME, если строка содержит только дату или время.
SUBDATE(date,INTERVAL expr unit) и SUBDATE(expr,days)
Если SUBDATE() вызывается со вторым аргументом, заданным в виде INTERVAL, функция является синонимом DATE_SUB(). Информацию об аргументе INTERVAL смотрите в разделе DATE_ADD().
SUBTIME(expr1,expr2)
Функция SUBTIME() возвращает expr1. expr2 выражается как значение в том же формате, что и expr1. Значение expr1 является выражением времени или даты и времени, а значение expr2 является выражением времени.
SYSDATE()
Возвращает текущую дату и время как значение в формате «YYYY-MM-DD HH: MM: SS» или YYYYMMDDHHMMSS, в зависимости от того, используется ли функция в строковом или в числовом контексте.
TIME(expr)
Извлекает часть, относящуюся ко времени, выражения expr и возвращает его в виде строки.
TIMEDIFF(expr1,expr2)
Функция TIMEDIFF() возвращает expr1 . expr2 выражается как значение времени. Значения expr1 и expr2 представляют собой выражения времени или даты и времени, но оба они должны быть одного типа.
TIMESTAMP (expr), TIMESTAMP (expr1, expr2)
С одним указанным аргументом эта функция возвращает выражение даты или даты и времени expr, как значение даты и времени. С двумя аргументами она добавляет выражение времени expr2 к выражению даты или даты и времени expr1 и возвращает результат как значение даты и времени.
TIMESTAMPADD(unit,interval,datetime_expr)
Эта функция добавляет целочисленное выражение interval к выражению даты или времени datetime_expr. Единицы измерения для интервала задаются аргументом unit, который может принимать одно из следующих значений:
Значение unit может быть указано с использованием одного из ключевых слов, как было показано выше, или с префиксом SQL_TSI_. Например, DAY и SQL_TSI_DAY являются действительными значениями.
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Возвращает целочисленную разницу между выражениями даты или даты и времени datetime_expr1 и datetime_expr2. Единицы измерения для результата задаются аргументом unit. Действительными для аргумента unit являются те же значения, которые были перечислены в описании функции TIMESTAMPADD().
TIME_FORMAT(time,format)
Эта функция используется так же, как и функция DATE_FORMAT(), но строка format может содержать указатели формата только для часов, минут и секунд.
Если значение времени содержит часть, относящуюся к часам, которая больше 23, указатели формата часов %H и %k дают значение, большее, чем обычный диапазон от 0 до 23. Другие указатели формата часов дают значение часа 12 по модулю.
TIME_TO_SEC(time)
Возвращает аргумент time, преобразованный в секунды.
TO_DAYS(date)
Возвращает номер дня (количество дней с 0-го года) для заданной даты date.
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
Если эта функция вызывается без аргумента, она возвращает временную метку Unix (секунды с «1970-01-01 00:00:00» UTC), как целое положительное число. Если UNIX_TIMESTAMP() вызывается с аргументом date, она возвращает значение аргумента, выраженное в секундах с «1970-01-01 00:00:00» UTC. date может быть строкой DATE, строкой DATETIME, TIMESTAMP или числом в формате YYMMDD или YYYYMMDD.
UTC_DATE, UTC_DATE()
Возвращает текущую дату UTC как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или числовом контексте.
UTC_TIME, UTC_TIME()
Возвращает текущее время UTC как значение в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте.
UTC_TIMESTAMP, UTC_TIMESTAMP()
Возвращает текущую дату и время UTC как значение «YYYY-MM-DD HH: MM: SS» или в формате YYYYMMDDHHMMSS, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.
WEEK(date[,mode])
Эта функция возвращает номер недели для заданной даты date. Форма WEEK() с двумя аргументами позволяет указать, будет ли неделя начинаться в воскресенье или в понедельник, и должно ли возвращаемое значение находиться в диапазоне от 0 до 53 или от 1 до 53. Если аргумент mode опущен, используется значение системной переменной default_week_format
WEEKDAY(date)
Возвращает индекс дня недели для заданной даты date (0 = понедельник, 1 = вторник, 6 = воскресенье).
WEEKOFYEAR(date)
Возвращает календарную неделю для заданной даты date как число в диапазоне от 1 до 53. WEEKOFYEAR() — это функция совместимости, эквивалентная WEEK(date,3).
YEAR(date)
Возвращает год для заданной даты date в диапазоне от 1000 до 9999 или 0 для даты.zero.
YEARWEEK(date), YEARWEEK(date,mode)
Возвращает год и неделю для заданной даты date. Параметр mode работает точно так же, как аргумент mode для функции WEEK(). Год в результате может отличаться от года в аргументе даты для первой и последней недели года.
Примечание. Номер недели отличается от того, что вернет функция WEEK() (0) для необязательных аргументов 0 или 1, так как WEEK() вернет неделю в контексте данного года.
Последнее обновление: 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 запроса и получать необходимую выборку данных.
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()
– Функция для различного вывода информации о времени.