SQL Query including time calculation

Question

Hi,

Had a good search here but can't see anything that gets my mind in gear for this one.

Basically I have a table call Diaries, this contains fields such as StartTime EndTime, I need to do a calculation that works out the difference in minutes between StartTime and EndTime. then for each row that fits that day I need to add up the result in minutes.

something like

Select DiaryID, TotalMinutes from (Select (EndTime - StartTime) / 60 AS SubTotalMins from Diary Where ID = Diary.ID) AS FirstSelect (Sum(FirstSelect.SubTotalMins) As TotalMinutes Where ID = Diary.ID

the above is rubbish of course but you can see what I am trying to work out.

for each row work out the difference between End and Start in minutes for all rows that match the where clause sum return the sum.

Any ideas?

Regards

Answer 1

If I understand correctly (for SQL Server at least)...

SELECT
    SUM(DATEDIFF(minute, StartTime, EndTime)),
    DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) AS [Date]
FROM
    Diary
WHERE
    DiaryID = @ID
GROUP BY
    DiaryID /* in case for all DiaryIDs */,
    DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0)

Note: this removes the time component from a "datetime" value. Works by calculating the number of days since zero (= 01 Jan 1900). It's the most efficient

DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0)

DATEDIFF in MSDN

Answer 2

I'm not sure it's specified in the SQL Standard, but most SQL implementations have some sort of function for determining intervals. It's really going to boil down to what flavor of SQL you're using.

If you're working with Oracle/PLSQL:

SELECT NumToDSInterval(enddate- startdate, 'MINUTE') FROM MyTable

In SQL Server/T-SQL:

SELECT DateDiff(n, startdate, enddate) FROM MyTable

In MySQL:

SELECT SubTime(enddate, startdate) FROM MyTable;

I'm sure there's one for SQLite and PostGre and any other flavor as well.