Функции Transact-SQL. SQL функции даты и времени Mssql функции работы с датой

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

Агрегатные функции

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

AVG

Вычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.

MIN и MAX

Определяют максимальное и минимальное значение из всех значений данных, содержащихся в столбце. Значения могут быть числовыми, строковыми или временными (дата/время).

SUM

Вычисляет общую сумму значений в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.

COUNT

Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).

COUNT_BIG

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей.

Скалярные функции

Скалярные функции Transact-SQL используются в создании скалярных выражений. (Скалярная функция выполняет вычисления над одним значением или списком значений, тогда как агрегатная функция выполняет вычисления над группой значений из нескольких строк.) Скалярные функции можно разбить на следующие категории:

    числовые функции;

    функции даты;

    строковые функции;

    системные функции;

    функции метаданных.

Эти типы функций рассматриваются в последующих разделах.

Числовые функции

Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:

Числовые функции Transact-SQL
Функция Синтаксис Описание Пример использования
ABS ABS(n)

Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n.

SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90
ACOS, ASIN, ATAN, ATN2 ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)

Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT.
COS, SIN, TAN, COT COS(n), SIN(n), TAN(n), COT(n)

Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT.
DEGREES, RADIANS DEGREES(n), RADIANS(n)

Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот.

SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5
CEILING CEILING(n)

Округляет число до большего целого значения.

SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9
ROUND ROUND(n, p, [t])

Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону).

SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000
FLOOR FLOOR(n)

Округляет до меньшего целого значения.

SELECT FLOOR(5.88) -- Вернет 5
EXP EXP(n)

Вычисляет значение e n .
LOG, LOG10 LOG(n), LOG10(n)

LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n.
PI PI()

Возвращает значение π (3,1415).
POWER POWER(x, y)

Вычисляет значение x y .
RAND RAND()

Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1.
ROWCOUNT_BIG ROWCOUNT_BIG()

Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT.
SIGN SIGN(n)

Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное.
SQRT, SQUARE SQRT(n), SQUARE(n)

SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n.

Функции даты

Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже:

Функции даты Transact-SQL
Функция Синтаксис Описание Пример использования
GETDATE GETDATE()

Возвращает текущую системную дату и время.

SELECT GETDATE()
DATEPART DATEPART (item, date)

Возвращает указанную в параметре item часть даты date в виде целого числа.

Вернет 1 (Январь) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012")
DATENAME DATENAME (item, date)

Возвращает указанную в параметре item часть даты date в виде строки символов.

Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012")
DATEDIFF DATEDIFF (item, dat1, dat2)

Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item.

Вернет 19 (19 лет промежуток между датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") -- Вернет 7305 (7305 дней промежуток между датами) SELECT DATEDIFF(day, "01.01.1990", "01.01.2010")
DATEADD DATEADD (item, n, date)

Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.)

Прибавит 3 дня к текущей дате SELECT DATEADD(day, 3, GETDATE())

Строковые функции

Строковые функции манипулируют значениями столбцов, которые обычно имеют символьный тип данных. Поддерживаемые в Transact-SQL строковые функции и их краткое описание приводятся в таблице ниже:

Строковые функции Transact-SQL
Функция Синтаксис Описание Пример использования
ASCII, UNICODE ASCII(char), UNICODE(char)

Преобразовывает указанный символ в соответствующее целое число кода ASCII.

SELECT ASCII("W") -- 87 SELECT UNICODE("ю") -- 1102
CHAR, NCHAR CHAR(int), NCHAR(int)

Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ.

SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ю"
CHARINDEX CHARINDEX (str1, str2)

Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0

Вернет 5 SELECT CHARINDEX ("морф", "полиморфизм")
DIFFERENCE DIFFERENCE (str1, str2)

Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII.

Вернет 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT LEFT (str, length), RIGHT (str, length)

Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT.

DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "Синх" SELECT LEFT(@str, 4) -- Вернет "зация" SELECT RIGHT(@str, 5)
LEN LEN(str)

Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы.
LOWER, UPPER LOWER(str), UPPER(str)

Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные.

DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "СИНХРОНИЗАЦИЯ" SELECT UPPER(@str) -- Вернет "синхронизация" SELECT LOWER(@str)
LTRIM, RTRIM LTRIM(str), RTRIM(str)

Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки.
QUOTENAME QUOTENAME (char_string)

Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями.

DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "[Синхронизация]" SELECT QUOTENAME(@str)
PATINDEX PATINDEX (%p%, expr)

Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен.

Вернет 4 SELECT PATINDEX("%хро%", "Синхронизация")
REPLACE REPLACE (str1, str2, str3)

Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3.

Вернет "Десинхронизация" SELECT REPLACE("Синхронизация", "Синхр", "Десинхр")
REPLICATE REPLICATE (str, i)

Повторяет i раз строку str.

Вернет "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE REVERSE (str)

Выводит строку str в обратном порядке.

Вернет "яицазинорхниС" SELECT REVERSE("Синхронизация")
SOUNDEX SOUNDEX (str)

Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII.
SPACE SPACE (length)

Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length).
STR STR (f[, len[, d]])

Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить.

Вернет "3.14" SELECT STR (3.1415, 4, 2)
STUFF STUFF (str1, a, length, str2)

Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2.

Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand")
SUBSTRING SUBSTRING (str1, a, length)

Извлекает из строки str, начиная с позиции a, подстроку длиной length.

Системные функции

Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.

В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:

Системные функции Transact-SQL
Функция Синтаксис Описание Пример использования
CAST CAST (w AS type [(length)]

Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением.

Вернет 3 SELECT CAST (3.1258 AS INT)
COALESCE COALESCE (a1, a2)

Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null.
COL_LENGTH COL_LENGTH (obj, col)

Возвращает длину столбца col объекта базы данных (таблицы или представления) obj.

Вернет 4 SELECT COL_LENGTH ("Employee", "Id")
CONVERT CONVERT (type[(length)], w)

Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных.
CURRENT_TIMESTAMP CURRENT_TIMESTAMP

Возвращает текущие дату и время.
CURRENT_USER CURRENT_USER

Возвращает имя текущего пользователя.
DATALENGTH DATALENGTH (z)

Возвращает число байтов, которые занимает выражение z.

Этот запрос возвращает длину каждого поля SELECT DATALENGTH(FirstName) FROM Employee
GETANSINULL GETANSINULL ("dbname")

Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL.
ISNULL ISNULL (expr, value)

Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value.
ISNUMERIC ISNUMERIC (expr)

Определяет, имеет ли выражение expr действительный числовой тип.
NEWID NEWID()

Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER.
NEWSEQUENTIALID NEWSEQUENTIALID()

Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.)
NULLIF NULLIF (expr1, expr2)

Возвращает значение null, если значения выражений expr1 и expr2 одинаковые.

Запрос возвращает NULL для проекта, -- у которого Number = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY SERVERPROPERTY (propertyname)

Возвращает информацию о свойствах сервера базы данных.
SYSTEM_USER SYSTEM_USER

Возвращает ID текущего пользователя.
USER_ID USER_ID ()

Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя.
USER_NAME USER_NAME ()

Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя.

Функции метаданных

По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием:

Функции метаданных Transact-SQL
Функция Синтаксис Описание Пример использования
COL_NAME COL_NAME (tab_id, col_id)

Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id.

Вернет имя столбца "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMNPROPERTY COLUMNPROPERTY (id, col, property)

Возвращает информацию об указанном столбце.

Вернет значение свойства PRECISION -- для столбца Id таблицы Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision")
DATABASEPROPERTY DATABASEPROPERTY (database, property)

Возвращает значение свойства property базы данных database.

Вернет значение свойства IsNullConcat -- для базы данных SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID DB_ID ()

Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных.
DB_NAME DB_NAME ()

Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных.
INDEX_COL INDEX_COL (table, i, no)

Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе.
INDEXPROPERTY INDEXPROPERTY (obj_id, index_name, property)

Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства.
OBJECT_NAME OBJECT_NAME (obj_id)

Возвращает имя объекта базы данных, имеющего идентификатор obj_id.

SELECT OBJECT_NAME(245575913);
OBJECT_ID OBJECT_ID (obj_name)

Возвращает идентификатор объекта obj_name базы данных.

Вернет 245575913 - ID таблицы Employee SELECT OBJECT_ID("Employee")
OBJECTPROPERTY OBJECTPROPERTY (obj_id, property)

Возвращает информацию об объектах из текущей базы данных.

Получение текущих даты и времени

Для получения текущей даты (без времени) в формате 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(номер_дня) – возвращает дату в соответствии с номером дня.

Последнее обновление: 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

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
SELECT name, birth, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)

Найти всех пользователей у которых день рождение в следующем месяце в SQL
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;

Кроме вышеприведенных кейсов по работе с датами в SQL, рекомендую ознакомиться с документацией по следующим операторам:
NOW() – Возвращает текущую дату и время.
CURDATE() – Возвращает текущую дату.
CURTIME() – Возвращаем текущее время.
DATE() – Состоит из двух частей даты и времени.
EXTRACT() – Возвращает одно значения даты/времени.
DATE_ADD() – Добавляет до выборки указанное число дней/мину/часов и т.д.
DATE_SUB() – Вычитываем указанный интервал от даты.
DATEDIFF() – Возвращает значение времени между двумя датами.
DATE_FORMAT() – Функция для различного вывода информации о времени.

Работа с датами в SQl, как оказывается не такая сложная, и теперь вместо того чтобы вычислять периоды средствами PHP можно делать это еще на этапе выполнения SQL запроса и получать необходимую выборку данных.

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