SQL — Функции даты. Функции Transact-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 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); Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье. Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций: Вычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми. Определяют максимальное и минимальное значение из всех значений данных, содержащихся в столбце. Значения могут быть числовыми, строковыми или временными (дата/время). Вычисляет общую сумму значений в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми. Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null). Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT. Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей. Скалярные функции Transact-SQL используются в создании скалярных выражений. (Скалярная функция выполняет вычисления над одним значением или списком значений, тогда как агрегатная функция выполняет вычисления над группой значений из нескольких строк.) Скалярные функции можно разбить на следующие категории: числовые функции; функции даты; строковые функции; системные функции; функции метаданных. Эти типы функций рассматриваются в последующих разделах. Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже: Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n. SELECT ABS(-5.320) -- Вернет 5.320
SELECT ABS(8.90) -- Вернет 8.90 Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT. Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT. Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот. SELECT DEGREES(PI() / 4) -- Вернет 45
SELECT COS(RADIANS(60.0)) -- Вернет 0.5 Округляет число до большего целого значения. SELECT CEILING(-5.320) -- Вернет -5
SELECT CEILING(8.90) -- Вернет 9 Округляет значение 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 Округляет до меньшего целого значения. SELECT FLOOR(5.88) -- Вернет 5 Вычисляет значение e n . LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n. Возвращает значение π (3,1415). Вычисляет значение x y . Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1. Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT. Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное. SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n. Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже: Возвращает текущую системную дату и время. SELECT GETDATE() Возвращает указанную в параметре item часть даты date в виде целого числа. Вернет 1 (Январь)
SELECT DATEPART(month, "01.01.2012")
-- Вернет 4 (Wednesday)
SELECT DATEPART(weekday, "02.01.2012") Возвращает указанную в параметре item часть даты date в виде строки символов. Вернет January
SELECT DATENAME(month, "01.01.2012")
-- Вернет Wednesday
SELECT DATENAME(weekday, "02.01.2012") Вычисляет разницу между двумя частями дат 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") Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.) Прибавит 3 дня к текущей дате
SELECT DATEADD(day, 3, GETDATE()) Строковые функции манипулируют значениями столбцов, которые обычно имеют символьный тип данных. Поддерживаемые в Transact-SQL строковые функции и их краткое описание приводятся в таблице ниже: Преобразовывает указанный символ в соответствующее целое число кода ASCII. SELECT ASCII("W") -- 87
SELECT UNICODE("ю") -- 1102 Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ. SELECT CHAR(87) -- "W"
SELECT NCHAR(1102) -- "ю" Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0 Вернет 5
SELECT CHARINDEX ("морф", "полиморфизм") Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII. Вернет 2
SELECT DIFFERENCE ("spelling", "telling") Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT. DECLARE @str nvarchar(30) = "Синхронизация";
-- Вернет "Синх"
SELECT LEFT(@str, 4)
-- Вернет "зация"
SELECT RIGHT(@str, 5) Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы. Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные. DECLARE @str nvarchar(30) = "Синхронизация";
-- Вернет "СИНХРОНИЗАЦИЯ"
SELECT UPPER(@str)
-- Вернет "синхронизация"
SELECT LOWER(@str) Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки. Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями. DECLARE @str nvarchar(30) = "Синхронизация";
-- Вернет "[Синхронизация]"
SELECT QUOTENAME(@str) Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен. Вернет 4
SELECT PATINDEX("%хро%", "Синхронизация") Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3. Вернет "Десинхронизация"
SELECT REPLACE("Синхронизация", "Синхр", "Десинхр") Повторяет i раз строку str. Вернет "aBaBaBaBaB"
SELECT REPLICATE("aB", 5) Выводит строку str в обратном порядке. Вернет "яицазинорхниС"
SELECT REVERSE("Синхронизация") Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII. Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length). Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить. Вернет "3.14"
SELECT STR (3.1415, 4, 2) Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2. Note in a book
SELECT STUFF("Notebook", 5, 0," in a ")
-- Handbook
SELECT STUFF("Notebook", 1, 4, "Hand") Извлекает из строки str, начиная с позиции a, подстроку длиной length. Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных. В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием: Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением. Вернет 3
SELECT CAST (3.1258 AS INT) Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null. Возвращает длину столбца col объекта базы данных (таблицы или представления) obj. Вернет 4
SELECT COL_LENGTH ("Employee", "Id") Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных. Возвращает текущие дату и время. Возвращает имя текущего пользователя. Возвращает число байтов, которые занимает выражение z. Этот запрос возвращает длину каждого поля
SELECT DATALENGTH(FirstName) FROM Employee Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL. Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value. Определяет, имеет ли выражение expr действительный числовой тип. Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER. Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.) Возвращает значение null, если значения выражений expr1 и expr2 одинаковые. Запрос возвращает NULL для проекта,
-- у которого Number = "p1"
SELECT NULLIF(Number, "p1") FROM Project Возвращает информацию о свойствах сервера базы данных. Возвращает ID текущего пользователя. Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя. Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя. По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием: Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id. Вернет имя столбца "LastName"
SELECT COL_NAME (OBJECT_ID("Employee"), 3) Возвращает информацию об указанном столбце. Вернет значение свойства PRECISION
-- для столбца Id таблицы Employee
SELECT COLUMNPROPERTY (OBJECT_ID("Employee"),
"Id", "precision") Возвращает значение свойства property базы данных database. Вернет значение свойства IsNullConcat
-- для базы данных SampleDb
SELECT DATABASEPROPERTY ("SampleDb",
"IsNullConcat") Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных. Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных. Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе. Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства. Возвращает имя объекта базы данных, имеющего идентификатор obj_id. SELECT OBJECT_NAME(245575913); Возвращает идентификатор объекта obj_name базы данных. Вернет 245575913 - ID таблицы Employee
SELECT OBJECT_ID("Employee") Возвращает информацию об объектах из текущей базы данных. Приветствую Вас, уважаемые читатели блога сайт. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке 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 и преобразует их в дату. Пример.
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;
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 - годыАгрегатные функции
Скалярные функции
Числовые функции
Числовые функции Transact-SQL
Функция
Синтаксис
Описание
Пример использования
ABS
ABS(n)
ACOS, ASIN, ATAN, ATN2
ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)
COS, SIN, TAN, COT
COS(n), SIN(n), TAN(n), COT(n)
DEGREES, RADIANS
DEGREES(n), RADIANS(n)
CEILING
CEILING(n)
ROUND
ROUND(n, p, [t])
FLOOR
FLOOR(n)
EXP
EXP(n)
LOG, LOG10
LOG(n), LOG10(n)
PI
PI()
POWER
POWER(x, y)
RAND
RAND()
ROWCOUNT_BIG
ROWCOUNT_BIG()
SIGN
SIGN(n)
SQRT, SQUARE
SQRT(n), SQUARE(n)
Функции даты
Функции даты Transact-SQL
Функция
Синтаксис
Описание
Пример использования
GETDATE
GETDATE()
DATEPART
DATEPART (item, date)
DATENAME
DATENAME (item, date)
DATEDIFF
DATEDIFF (item, dat1, dat2)
DATEADD
DATEADD (item, n, date)
Строковые функции
Строковые функции Transact-SQL
Функция
Синтаксис
Описание
Пример использования
ASCII, UNICODE
ASCII(char), UNICODE(char)
CHAR, NCHAR
CHAR(int), NCHAR(int)
CHARINDEX
CHARINDEX (str1, str2)
DIFFERENCE
DIFFERENCE (str1, str2)
LEFT, RIGHT
LEFT (str, length), RIGHT (str, length)
LEN
LEN(str)
LOWER, UPPER
LOWER(str), UPPER(str)
LTRIM, RTRIM
LTRIM(str), RTRIM(str)
QUOTENAME
QUOTENAME (char_string)
PATINDEX
PATINDEX (%p%, expr)
REPLACE
REPLACE (str1, str2, str3)
REPLICATE
REPLICATE (str, i)
REVERSE
REVERSE (str)
SOUNDEX
SOUNDEX (str)
SPACE
SPACE (length)
STR
STR (f[, len[, d]])
STUFF
STUFF (str1, a, length, str2)
SUBSTRING
SUBSTRING (str1, a, length)
Системные функции
Системные функции Transact-SQL
Функция
Синтаксис
Описание
Пример использования
CAST
CAST (w AS type [(length)]
COALESCE
COALESCE (a1, a2)
COL_LENGTH
COL_LENGTH (obj, col)
CONVERT
CONVERT (type[(length)], w)
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
CURRENT_USER
CURRENT_USER
DATALENGTH
DATALENGTH (z)
GETANSINULL
GETANSINULL ("dbname")
ISNULL
ISNULL (expr, value)
ISNUMERIC
ISNUMERIC (expr)
NEWID
NEWID()
NEWSEQUENTIALID
NEWSEQUENTIALID()
NULLIF
NULLIF (expr1, expr2)
SERVERPROPERTY
SERVERPROPERTY (propertyname)
SYSTEM_USER
SYSTEM_USER
USER_ID
USER_ID ()
USER_NAME
USER_NAME ()
Функции метаданных
Функции метаданных Transact-SQL
Функция
Синтаксис
Описание
Пример использования
COL_NAME
COL_NAME (tab_id, col_id)
COLUMNPROPERTY
COLUMNPROPERTY (id, col, property)
DATABASEPROPERTY
DATABASEPROPERTY (database, property)
DB_ID
DB_ID ()
DB_NAME
DB_NAME ()
INDEX_COL
INDEX_COL (table, i, no)
INDEXPROPERTY
INDEXPROPERTY (obj_id, index_name, property)
OBJECT_NAME
OBJECT_NAME (obj_id)
OBJECT_ID
OBJECT_ID (obj_name)
OBJECTPROPERTY
OBJECTPROPERTY (obj_id, property)
Получение текущей даты и времени.
Результат: 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