Thread: [Squirrel-sql-users] MySQL TimeDiff()
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Paul S. <pa...@sl...> - 2008-08-22 12:24:39
|
I'm trying to use the TimeDiff function in MySQL (5.0) but I just get <Error> in the result set. I've tried the Connector/J driver both 5.0 and 5.1. The function isn't in the list of Date/Time functions on the Objects tab. With the Connector/ODBC driver the function isn't in the list either but I do get an answer in the result set although it won't go negative when it should. Is there a reason for the TimeDiff function not working with the Java drivers? This is critical to some of the reports I need to develop and I'll have to find another way if TimeDiff won't work. Naturally the function works correctly in the horrid MySQL Query Browser which you might guess I don't like J. TIA, Paul |
From: Robert M. <rob...@gm...> - 2008-08-25 01:17:44
|
On Fri, Aug 22, 2008 at 8:24 AM, Paul Slater <pa...@sl...> wrote: > I'm trying to use the TimeDiff function in MySQL (5.0) but I just get > <Error> in the result set. I've tried the Connector/J driver both 5.0 and > 5.1. The function isn't in the list of Date/Time functions on the Objects > tab. With the Connector/ODBC driver the function isn't in the list either > but I do get an answer in the result set although it won't go negative when > it should. Is there a reason for the TimeDiff function not working with the > Java drivers? This is critical to some of the reports I need to develop and > I'll have to find another way if TimeDiff won't work. Naturally the function > works correctly in the horrid MySQL Query Browser which you might guess I > don't like J. > Paul, This looks like a driver limitation to me. The driver is reporting that the column type of the expression TIMEDIFF(x,y) as "DATE" (92). However, when we attempt to get the result value from the result set as a time value, it throws a SQLException claiming "Bad Format". It should be an interval data type - though there is no support for this in JDBC. If you are merely executing a query that applies the TIMEDIFF function, you can use convert as in: select convert(TIMEDIFF('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002'), char) from a However, if you are storing that in a table and trying to view it using the table contents tab, that is not possible at the moment given the above. Perhaps you could file a request with the MySQL jdbc vendor to identify this column (using getColumnTypeName) as an interval, or timespan or something that indicates that it is a time interval. We could also update the MySQL plugin to add a custom handler for this type. You can file a feature request here: http://sourceforge.net/tracker/?atid=393417&group_id=28383 Rob |
From: Robert M. <rob...@gm...> - 2008-08-25 11:41:05
|
On Mon, Aug 25, 2008 at 5:24 AM, Paul Slater <pa...@sl...> wrote: > Rob, > > > > Many thanks for looking at this. When I went to test your example I put the > times around the other way to that I was testing and it worked! I was > looking for timediff( started, finished) but found this worked correctly…. > > > > select t.started,t.finished, timediff( t.finished, t.started ) > > from timetest t > > > > I also wanted to use it with a where clause which is fine… > > > > select t.started,t.finished, timediff( t.finished, t.started ) > > from timetest t > > where timediff( t.finished, t.started ) < '09:00:00' > > > > The method you sent works well to extract the difference and gets the sign > correct > > > > select t.started,t.finished, convert( timediff( t.started,t.finished ), > char ) > > from timetest t > > where timediff( t.finished, t.started ) < '09:00:00' > > > > As I'm only (at the moment) using this in select statements you've given me > an answer to the problem. Again, thanks for your trouble. > Glad to hear it! Rob |