CODEWITHSUNDEEP
sqlt-sql
SeanFlynn
SeanFlynn

Reputation: 453

SQL datediff time calculation

I have these fields in my T-SQL database - startTime and endTime and processTime.
The process time is the amount of time from start to end time.
So, one startTime example is 22:50:59 and its endTime is 23:14:23.
Process time would be .39 (The current code):

SELECT convert( decimal(18,0), totalItems 
        /(datediff(ss,sessionStartTime,sessionEndTime)/60.00/60.00) ) 
  AS processTime

This seems to be working for the most part, but it doesn't work when the startTime and endTime span across a day - for example, a startTime is 23:17:27 and an endTime of 00:31:23.

In other words, they started at 10:17 p.m., and finished at about 12:31 a.m. the following morning, but SQL isn't seeing this, because the processTime is displaying as a negative number (-22.77)...
How do I convert these negative times into positive times?

I do have separate date fields - a startDate, and an endDate.

Upvotes: 2

Views: 8501

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

I'm assuming that you're working in MS SQL and for some reason you're stripping out the date component so the date is always the same. You can fix this by testing the date and if the end time is less then the start time add a day to the end time.

SELECT CONVERT(DECIMAL(18, 0), totalitems / 
     ( CASE 
             WHEN sessionendtime < sessionstarttime THEN Datediff(ss, sessionstarttime, Dateadd(dd, 1, 
       sessionendtime )) 
            ELSE Datediff(ss, sessionstarttime, sessionendtime ) 
       END)/ 
       60.00/60.00)

This solution is problematic if the end time is greater than 24 hours after the start time

Upvotes: 1

Chains
Chains

Reputation: 13157

I'm assuming you're including dates...(?)

Here is a sample I ran -- seems to work as expected:

declare @start datetime
declare @end datetime

set @start = '2011-09-06 23:17:27'
set @end = '2011-09-07 00:31:23'

select 
    datediff(second,@start,@end) as seconds, 
    datediff(minute,@start,@end) as minutes,
    datediff(hour,@start,@end) as hours,
    datediff(day,@start,@end) as days,
    datediff(month,@start,@end) as months,
    datediff(year,@start,@end) as years

Here's the results:

seconds     minutes     hours       days        months      years
----------- ----------- ----------- ----------- ----------- -----------
4436        74          1           1           0           0

(1 row(s) affected)

One thing you might do is re-check the scope of your decimal conversion. E.g.: this produces a different results, depending on where you put the parens before the division:

declare @start datetime
declare @end datetime

set @start = '2011-09-06 23:17:27'
set @end = '2011-09-07 00:31:23'

select convert(decimal(18,0), (datediff(ss,@start,@end)))/60.00/60.00 as NEW_processTime,
       convert(decimal(18,0), (datediff(ss,@start,@end)/60.00/60.00)) as ORIGINAL_processTime

Here are the results of that:

NEW_processTime                         ORIGINAL_processTime
--------------------------------------- ---------------------------------------
1.23222221666                           1

(1 row(s) affected)

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36431

First, what database engine are we talking about? MS SQL Server? MySQL? Something else?

I mostly work with MS SQL Server and I don't know the datediff implementations of other database engines, but your problem probably occurs because your startTime and endTime fields contain only time information, but no date:

If you give datediff only 23:17:27 and 00:31:23, how should it "decide"/"know" whether you mean the same day?
But if you would set startTime to 2011-09-06 23:17:27 and endTime to 2011-09-07 00:31:23, it would work because datediff would definitely know that endTime is not at the same day as startTime.

Upvotes: 1

Related Questions