0

I want to get the min and max difference between two different time columns from my database but I'm not sure what function I am supposed to use to do this. I spent some time looking online and found something called timediff which I thought would work but apparently it doesn't exist. What function should I be using to carry out this statement? I made an attempt using timediff which doesn't work but should help anyone understand what I am trying to do.

SELECT EMPLOYEEID, COUNT(ORDER_TYPE) AS TYPECOUNT,
MIN(TIMEDIFF(ORDER_ACCEPTED_TIME,ORDER_COMPLETION_TIME)) AS MINPREP,
MAX(TIMEDIFF(ORDER_ACCEPTED_TIME, ORDER_COMPLETION_TIME)) AS MAXPREP 
FROM ORDERS WHERE ORDERDATE BETWEEN '4/01/2015' AND '4/30/2015' AND ORDER_TYPE ='Breakfast' 
GROUP BY EMPLOYEEID

The above is the statement that I tried previously using the timediff method which doesn't work in my program. In addition, I am writing this code in java and am using jdbc connectivity and therefore am trying to write this statement into a string which gets passed as a statement later on

jpw
  • 44,361
  • 6
  • 66
  • 86
Nick Karaolis
  • 127
  • 1
  • 3
  • 13
  • Have a look at this: http://stackoverflow.com/a/11618722/1837329 – IndieTech Solutions Apr 06 '15 at 19:15
  • What database are you using? Time and date types and functions differ a bit between various databases. – jpw Apr 06 '15 at 19:22
  • @jpw it's in java so using jdbc connectivity – Nick Karaolis Apr 06 '15 at 19:24
  • @NickKaraolis But what database engine are you using? MySQL, Oracle, MSSQL or something else? – jpw Apr 06 '15 at 19:25
  • @jpw I'm using apache derby as the engine – Nick Karaolis Apr 06 '15 at 19:31
  • Looks like you want the TIMESTAMPDIFF function. Looks at the [documentation](https://db.apache.org/derby/docs/10.7/ref/rrefjdbc88908.html#rrefjdbc88908) and at this [answer](http://stackoverflow.com/questions/2324522/how-to-get-the-hour-difference-between-2-datetimestamp-on-derby-db/2324557#2324557) – jpw Apr 06 '15 at 19:47

1 Answers1

2

If you are using **SQL Server**, Try DATEDIFF

Note: DATEDIFF won't give you a 'format'. It just returns an integer. So you'll need to cast/format appropriately.You can use the 108 format to CAST it to minutes/seconds.

Example: 

DECLARE @dt1 datetime
DECLARE @dt2 datetime
SELECT @dt1 = '20090316 12:00:00.000', @dt2 = GETDATE()

SELECT CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @dt1, @dt2), 0), 114)

If you are using **Oracle** , there no similar function , BUT here's the way on how to do it:

ENDDATE - STARTDATE will give you a number that corresponds to the number of days between the two dates.

If you want the result in hours, multiply by 24; if minutes, multiply by 24*60 and so forth.

You can also convert the result to an INTERVAL. There are two type of intervals: NUMTODSINTERVAL(ENDDATE - STARTDATE, 'DAY') or NUMTOYMINTERVAL(ENDDATE - STARTDATE, 'DAY')

Source

If you are using Derby,there is a TIMESTAMPDIFF:

TIMESTAMPDIFF( interval, timestampExpression1, timestampExpression2 )

Apache official documentation

IndieTech Solutions
  • 2,527
  • 1
  • 21
  • 36