Как найти разницу во времени sql

DATEDIFF ( datepart , startdate , enddate )  

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(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');

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  

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);  

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());  

USE AdventureWorks2012;  
GO  
SELECT DATEDIFF(day,
    (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),  
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));  

SELECT DATEDIFF(day,
   '2007-05-07 09:53:01.0376635',
   '2007-05-08 09:53:01.0376635');  

USE AdventureWorks2012;  
GO  
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1)
    AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  
USE AdventureWorks2012;  
GO  
SELECT
    DATEDIFF(
            day,
            '2007-05-07 09:53:01.0376635',
            DATEADD(day, 1, SYSDATETIME())
        ) AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  

USE AdventureWorks2012;  
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;  

USE AdventureWorks2012;  
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  

-- 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

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  

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee),  
    (SELECT MAX(HireDate) FROM dbo.DimEmployee))   
FROM dbo.DimEmployee;  
  

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day,
    '2007-05-07 09:53:01.0376635',
    '2007-05-08 09:53:01.0376635') FROM DimCustomer;  

-- Uses AdventureWorks  
  
SELECT FirstName, LastName,
    DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        DepartmentName), SYSDATETIME()) AS RowNumber  
FROM dbo.DimEmployee;  

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, DepartmentName,
    DATEDIFF(year, MAX(HireDate)  
        OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue  
FROM dbo.DimEmployee  

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. SQL elapsed-time calculations for DATE, DATETIME, and TIMESTAMP data types are done with DATEDIFF function, with the following syntax:

DATEDIFF( date_expression_1, date_expression_2 );

Any valid date or date-time values are acceptable. Only the date portion of DATETIME or TIMESTAMP values are used, regardless of any time portion..

For background context, please check out our SQL Dates & Times blog entry.

DATEDIFF Excludes The Ending Date

DATEDIFF returns the exclusive relationship of date_expression_1 to date_expression_2 (that is to say, not including the ending day). To be as clear on this oft-confusing point, the number of days between the first and third of January is:

SELECT
   DATEDIFF( '2021-01-03', '2021-01-01' )
   AS 'Days From 1 and 3 January' ;

DATEDIFF Between Dates

To get to Cinco de Mayo from New Year’s Day:

SELECT
  DATEDIFF( '2021-05-05', '2021-01-01' )
  AS "From New Year’s Day to Cinco de Mayo" ;

It’s 124 days from New Year’s Day until Cinco de Mayo begins.

SELECT
  DATEDIFF( '2021-01-01', '2021-05-05' )
  AS "From Cinco de Mayo to New Year’s Day" ;

The negative number shows that New Year’s Day is 124 days before Cinco de Mayo.

DATEDIFF Ignores Time Information — Use TIMEDIFF

As its name suggests, DATEDIFF ignores any time portion supplied. The related function, TIMEDIFF, honors time portions, returning a sting with the difference, as shown here:

SELECT
  DATEDIFF('2000:01:01 00:00:01', '2000:01:01 00:00:00')
    AS 'DATEDIFF ignores time',
  TIMEDIFF('2000:01:01 00:00:01', '2000:01:01 00:00:00')
    AS 'TIMEDIFF honors time';

Note that TIMEDIFF doesn’t calculate negative (before) times; instead it reports a misleading “bad time format” error if argument 2 is later than argument 1.

Using DATEDIFF To Shape Results

In addition to using DATEDIFF to output elapsed times, it’s useful for restricting rows returned from a SQL Select. In the following example DATEDIFF is used to filter a user’s orders to those which took more than ten days between ordering and shipment.

SELECT order, order_time, ship_time
FROM purchases
WHERE DATEDIFF(order_time, ship_time) > 10;  

Use TIMESTAMPDIFF For Specific Units of Time

DATEDIFF and TIMEDIFF are great if their output formats are what’s needed. TIMESTAMPDIFF is suited for specific units of elapsed time. The following example shows how to get a difference in each of the supported units:

SET @start = '2023-01-01 00:00:00';
SET @stop = '2023-12-31 23:59:59' ;
SELECT
  timestampdiff(MICROSECOND, @start, @stop) AS 'microseconds',
  timestampdiff(SECOND, @start, @stop) AS 'seconds',
  timestampdiff(MINUTE, @start, @stop) AS 'minutes',
  timestampdiff(HOUR, @start, @stop) AS 'hours',
  timestampdiff(DAY, @start, @stop) AS 'days',
  timestampdiff(WEEK, @start, @stop) AS 'weeks',
  timestampdiff(MONTH, @start, @stop) AS 'months',
  timestampdiff(QUARTER, @start, @stop) AS 'quarters',
  timestampdiff(YEAR, @start, @stop) AS 'years';

So Many Date and Time Functions

Because time is such a central part of our experience, there are many date and time functions available. The feature set and syntax vary by databases, so becoming familiar with the appropriate documentation is vital. This is the very lengthy MySQL Date and Time manual page.

Conclusion

We’ve expanded upon our SQL Dates & Times blog entry with examples of calculating the difference between dates and times with DATEDIFF, TIMEDIFF,  and TIMESTAMPDIFF, along with a quick aside on how to use these functions to shape the results set from a SQL Select statement.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.