Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
[firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Hi all I would have attached a screen shot of the sample data but I guess that is not possible. What I am trying to achieve right now is get the time difference in hh:mm format to the user tStartTime and tEndTime field values 26.08.2014, 08:38:00.00026.08.2014, 09:45:00.000 26.08.2014, 10:00:00.00026.08.2014, 10:45:00.000 26.08.2014, 11:00:00.00026.08.2014, 12:00:00.000 26.08.2014, 11:30:00.00026.08.2014, 12:15:00.000 26.08.2014, 00:15:00.00026.08.2014, 13:30:00.000 and the following is the query SELECT sProdLineWorkProcess.*, (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) - (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) * 60)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nTotalMinutesWorked FROM sProdLineWorkProcess JOIN tProdLineWork ON tProdLineWork.iID = sProdLineWorkProcess.iPID WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26' --GROUP BY sProdLineWorkProcess.iEmployeeID The calculated field values are as follows: 1767 04545 1060 1-1545 1315795 Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15 mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins. Please advise on what I need to do correctly to get 0 hour and 45 mins for both 2nd and 4th row. Thanks and regards Bhavbhuti
Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
On 4-10-2014 14:11, venussof...@gmail.com [firebird-support] wrote: I would have attached a screen shot of the sample data but I guess that is not possible. What I am trying to achieve right now is get the time difference in hh:mm format to the user This behavior is documented at http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-intfunc-datediff.html DATEDIFF doesn't look at any smaller units than the one specified in the first argument. As a result, * “datediff (year, date '1-Jan-2009', date '31-Dec-2009')” returns 0, but * “datediff (year, date '31-Dec-2009', date '1-Jan-2010')” returns 1 Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15 mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins. It only looks at hour fields (and higher). The difference between 11 and 12 is 1. Please advise on what I need to do correctly to get 0 hour and 45 mins for both 2nd and 4th row. You need to diff minutes only and divide by 60 for hours. If you want something 1 hour and 30 minutes for 90 minutes, you need to use modulus 60. Mark -- Mark Rotteveel
Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Hi all I would have attached a screen shot of the sample data but I guess that is not possible. What I am trying to achieve right now is get the time difference in hh:mm format to the user tStartTime and tEndTime field values 26.08.2014, 08:38:00.00026.08.2014, 09:45:00.000 26.08.2014, 10:00:00.00026.08.2014, 10:45:00.000 26.08.2014, 11:00:00.00026.08.2014, 12:00:00.000 26.08.2014, 11:30:00.00026.08.2014, 12:15:00.000 26.08.2014, 00:15:00.00026.08.2014, 13:30:00.000 and the following is the query SELECT sProdLineWorkProcess.*, (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) - (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) * 60)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nTotalMinutesWorked FROM sProdLineWorkProcess JOIN tProdLineWork ON tProdLineWork.iID = sProdLineWorkProcess.iPID WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26' --GROUP BY sProdLineWorkProcess.iEmployeeID The calculated field values are as follows: 1767 04545 1060 1-1545 1315795 Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15 mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins. Please advise on what I need to do correctly to get 0 hour and 45 mins for both 2nd and 4th row. If you only care about hours and minutes, then your attempt is pretty close to one possible solution, Bhavbhuti. Try something similar to (assuming sProdLineWorkProcess has a unique ID field): with tmp(ID, nHours, nMinutes) as (SELECT plwp.ID, DATEDIFF(HOUR, plwp.tStartTime, plwp.tEndTime), DATEDIFF(MINUTE, plwp.tStartTime, plwp.tEndTime) - (DATEDIFF(HOUR, plwp.tStartTime, plwp.tEndTime) * 60) FROM sProdLineWorkProcess plwp JOIN tProdLineWork plw ON plw.iID = plwp.iPID WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26') select plwp.*, iif(nMinutes 0, nHours - 1, nHours) nHoursWorked, iif(nMinutes 0, nMinutes + 60, nMinutes) nMinutesWorked from tmp join sProdLineWorkProcess plwp on tmp.ID = plwp.ID HTH, Set
