Команда INTERVAL
Команда INTERVAL позволяет прибавлять к дате и отнимать от нее определенные промежутки времени.
После команды INTERVAL можно указывать определенную часть даты (день, месяц или год и тп), к примеру, так INTERVAL 1 DAY или INTERVAL 3 MONTH, или сразу несколько частей.
В этом случае значения даты берутся в кавычки, пример: давайте прибавим 1 год и 3 месяца — это будет выглядеть так: INTERVAL ‘1-3’ YEAR_MONTH.
Мы указываем, что хотим год и месяц (вот так: YEAR_MONTH), и перед этим в кавычках пишем сначала значение для года, потом значение для месяца. Между ними ставим разделитель (он может быть абсолютно любым).
Синтаксис
SELECT дата + INTERVAL что_прибавить FROM имя_таблицы WHERE условие
SELECT дата — INTERVAL что_отнять FROM имя_таблицы WHERE условие
Команды
- SECOND секунды
- MINUTE минуты
- HOUR часы
- DAY дни
- MONTH месяцы
- YEAR года
- MINUTE_SECOND минуты:секунды
- HOUR_MINUTE часы:минуты
- DAY_HOUR дни часы
- YEAR_MONTH года-месяцы
- HOUR_SECOND часы:минуты:секунды
- DAY_MINUTE дни часы:минуты
- DAY_SECOND дни часы:минуты:секунды
Таблицы для примеров
| id айди |
name событие |
datetime дата и время |
|---|---|---|
| 1 | событие 1 | 2010-03-01 12:40:50 |
| 2 | событие 2 | 2011-04-02 13:41:51 |
| 3 | событие 3 | 2012-05-03 14:42:52 |
Пример
В данном примере при выборке из таблицы к дате прибавляется 1 день:
SELECT *, datetime + INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name событие |
datetime дата |
new_date новая дата |
|---|---|---|---|
| 1 | событие 1 | 2010-03-01 12:01:02 | 2010-03- 02 12:01:02 |
| 2 | событие 2 | 2011-04-02 13:02:03 | 2011-05- 03 13:02:03 |
| 3 | событие 3 | 2012-05-03 14:03:04 | 2012-06- 04 14:03:04 |
Пример
В данном примере от даты отнимается 1 день:
SELECT *, datetime — INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2010- 02-28 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2011-05- 01 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2012-06- 02 14:03:04 |
Пример
В данном примере к дате прибавляется 1 год и 2 месяца:
SELECT *, date + INTERVAL «1-2» YEAR_MONTH as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-05 -01 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-06 -02 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-07 -03 14:03:04 |
Разделитель в запросе не имеет значения. Он может быть любым символом:
SELECT *, date + INTERVAL «1:2» YEAR_MONTH as new_date FROM events
Можно использовать даже несколько символов:
SELECT *, date + INTERVAL «1. 2» YEAR_MONTH as new_date FROM events
Можно использовать и пробел:
SELECT *, date + INTERVAL «1 2» YEAR_MONTH as new_date FROM events
Пример
В данном примере к дате прибавляется 1 год, 2 месяца и 1 день:
SELECT *, date + INTERVAL 1 YEAR + INTERVAL 2 MONTH + INTERVAL 1 DAY as new_date FROM employees
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-05-02 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-06-03 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-07-04 14:03:04 |
Запрос можно переписать таким образом:
SELECT *, date + INTERVAL «1 2» YEAR_MONTH + INTERVAL 1 DAY as new_date FROM events
Пример
В данном примере к дате прибавляется 1 год, отнимаются 2 месяца и прибавляется 1 день:
SELECT *, date + INTERVAL 1 YEAR — INTERVAL 2 MONTH + INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-01-02 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-02-03 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-03-04 14:03:04 |
Смотрите также
- функцию DATE_ADD ,
которая прибавляет значение к дате - функцию DATE_SUB ,
которая вычитает значение из даты
Как записать промежуток времени в sql
T-SQL предоставляет ряд функций для работы с датами и временем:
-
GETDATE : возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime
SELECT GETDATE() -- 2017-07-28 21:34:55.830
SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830
SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744
SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777
SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00
SELECT DAY(GETDATE()) -- 28
SELECT MONTH(GETDATE()) -- 7
SELECT YEAR(GETDATE()) -- 2017
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)
SELECT DATEPART(month, GETDATE()) -- 7
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
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 дней
SELECT TODATETIMEOFFSET('2017-7-28 01:10:22', '+03:00')
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:30')
SELECT EOMONTH('2017-02-05') -- 2017-02-28 SELECT EOMONTH('2017-02-05', 3) -- 2017-05-31
SELECT DATEFROMPARTS(2017, 7, 28) -- 2017-07-28
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
Как вывести тот промежуток времени, который мне нужен?
он выводит все данные за 5 месяцев, но прихватывает ещё запись за месяц с другого года.
Кто подскажет, а то голову всю сломал
- Вопрос задан более трёх лет назад
- 232 просмотра
2 комментария
Оценить 2 комментария
DATEDIFF (Transact-SQL)
Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.
Сведения о функции, которая обрабатывает значительные отличия значений аргументов startdate и enddate, см. в статье DATEDIFF_BIG (Transact-SQL). Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Синтаксис
DATEDIFF ( datepart , startdate , enddate ) Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
datepart
Единицы, в которых функция DATEDIFF сообщает о разнице между startdate и enddate. К часто используемым единицам datepart относятся month или second .
Значение datepart не может быть указано в переменной или в виде строки в кавычках, например ‘month’ .
В приведенной ниже таблице перечислены все допустимые значения datepart. Функция DATEDIFF принимает либо полное имя datepart, либо любое приведенное сокращение полного имени.
| Имя datepart | Сокращение datepart |
|---|---|
| year | y, yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy |
| day | dd, d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.
startdate
Выражение, которое может быть разрешено в одно из следующих значений.
Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.
enddate
См. описание аргумента startdate.
Тип возвращаемых данных
int
Возвращаемое значение
Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.
Например, SELECT DATEDIFF(day, ‘2036-03-01’, ‘2036-02-28’); возвращает значение -2, указывая на то, что 2036 – високосный год. Это означает, что, если начать со startdDate «2036-03-01» и отсчитать -2 дня, будет достигнуто значение enddate «2036-02-28».
В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) DATEDIFF возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.
Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF возвращает значение 0.
При вычислении возвращаемого значения DATEDIFF использует компонент смещения часовых поясов для аргументов startdate или enddate.
Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.
Если переменной типа данных date присвоено только значение времени, в качестве недостающей части даты DATEDIFF задает значение по умолчанию: 1900-01-01 . Если переменной типа данных time или date присвоено только значение даты, в качестве недостающей части времени DATEDIFF задает значение по умолчанию: 00:00:00 . Если в одном из аргументов startdate или enddate указано только время, а в другом только дата, в качестве недостающей информации DATEDIFF задает значения по умолчанию.
Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF присваивает значения 0 недостающим частям другого аргумента.
Границы, задаваемые аргументом datepart
Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1.
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(weekday, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF вернет 0 для части week аргумента datepart.
Замечания
Используйте DATEDIFF в предложениях SELECT , WHERE , HAVING , GROUP BY и ORDER BY .
Функция DATEDIFF неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.
Указание SET DATEFIRST не влияет на DATEDIFF . DATEDIFF всегда считает воскресенье первым днем недели, чтобы обеспечить детерминизм работы функции.
DATEDIFF может переполняться с точностью до минуты или более низкой точностью, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для int.
Примеры
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
А. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration(startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT DATEDIFF(day, startDate, endDate) AS 'Duration' FROM dbo.Duration; -- Returns: 1 B. Указание определенных пользователем переменных в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают определенные пользователем переменные.
DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722'; DECLARE @enddate DATETIME2 = '2007-05-04 12:10:09.3312722'; SELECT DATEDIFF(day, @startdate, @enddate); C. Указание скалярных системных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные системные функции.
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME()); D. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
USE AdventureWorks2022; GO SELECT DATEDIFF(day, (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)); Д. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate используются символьные константы.
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635'); Е. Указание числовых выражений и скалярных системных функций в качестве конечной даты
В этом примере в качестве аргументов для enddate используются числовое выражение (GETDATE() + 1) и скалярные системные функции GETDATE и SYSDATETIME .
USE AdventureWorks2022; GO SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays FROM Sales.SalesOrderHeader; GO USE AdventureWorks2022; GO SELECT DATEDIFF( day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME()) ) AS NumberOfDays FROM Sales.SalesOrderHeader; GO G. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента startdate. используется ранжирующая функция.
USE AdventureWorks2022; GO SELECT p.FirstName, p.LastName ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number' FROM Sales.SalesPerson s INNER JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; H. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента startdate используется агрегатная оконная функция.
USE AdventureWorks2022; GO SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate, DATEDIFF(day, MIN(soh.OrderDate) OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total' FROM Sales.SalesOrderDetail sod INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN(43659, 58918); GO I. Определение разницы между startdate и enddate в виде строковых компонентов даты
-- DOES NOT ACCOUNT FOR LEAP YEARS DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100); DECLARE @years INT, @months INT, @days INT, @hours INT, @minutes INT, @seconds INT, @milliseconds INT; SET @date1 = '1900-01-01 00:00:00.000' SET @date2 = '2018-12-12 07:08:01.123' SELECT @years = DATEDIFF(yy, @date1, @date2) IF DATEADD(yy, -@years, @date2) < @date1 SELECT @years = @years-1 SET @date2 = DATEADD(yy, -@years, @date2) SELECT @months = DATEDIFF(mm, @date1, @date2) IF DATEADD(mm, -@months, @date2) < @date1 SELECT @months=@months-1 SET @date2= DATEADD(mm, -@months, @date2) SELECT @days=DATEDIFF(dd, @date1, @date2) IF DATEADD(dd, -@days, @date2) < @date1 SELECT @days=@days-1 SET @date2= DATEADD(dd, -@days, @date2) SELECT @hours=DATEDIFF(hh, @date1, @date2) IF DATEADD(hh, -@hours, @date2) < @date1 SELECT @hours=@hours-1 SET @date2= DATEADD(hh, -@hours, @date2) SELECT @minutes=DATEDIFF(mi, @date1, @date2) IF DATEADD(mi, -@minutes, @date2) < @date1 SELECT @minutes=@minutes-1 SET @date2= DATEADD(mi, -@minutes, @date2) SELECT @seconds=DATEDIFF(s, @date1, @date2) IF DATEADD(s, -@seconds, @date2) < @date1 SELECT @seconds=@seconds-1 SET @date2= DATEADD(s, -@seconds, @date2) SELECT @milliseconds=DATEDIFF(ms, @date1, @date2) SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','') + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','') + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','') + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','') + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','') + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) + CASE WHEN @milliseconds >0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END + ' seconds','') SELECT @result 118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
J. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration (startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT TOP(1) DATEDIFF(day, startDate, endDate) AS Duration FROM dbo.Duration; -- Returns: 1 K. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee), (SELECT MAX(HireDate) FROM dbo.DimEmployee)) FROM dbo.DimEmployee; L. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate используются символьные константы.
-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635') FROM DimCustomer; M. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента startdate. используется ранжирующая функция.
-- Uses AdventureWorks SELECT FirstName, LastName, DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber FROM dbo.DimEmployee; О. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента startdate используется агрегатная оконная функция.
-- Uses AdventureWorks SELECT FirstName, LastName, DepartmentName, DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue FROM dbo.DimEmployee