tenths of seconds not display when using time(1) in MySQL 5.6.19

I upgraded to MySQL 5.6.19 so that I could have fractional seconds in the TIME data type. I have a column that is now time(1), which should allow tenths of seconds. However, looking at the table using DbVisualizer, I don't see the tenths of seconds. I updated the MySQL driver to mysql-connector-java-5.1.31-bin.jar and restarted DbVisualizer, but still no tenths of seconds. From the command line interface to SQL, I can see my tenths of seconds are there: update erg set newtsec='00:05:06.7' where id=1; select * from erg where id=1; +----+---------------------+------+------------+-------+-------+-------+--------+------+------+-------+---------+ | id | dt | who | newtsec | tsec | ap | newap | meters | aW | kCal | hrate | remarks | +----+---------------------+------+------------+-------+-------+-------+--------+------+------+-------+---------+ | 1 | 2014-04-02 07:10:00 | test | 00:05:06.7 | 306.7 | 153.4 | NULL | 1000 | 97.0 | 54 | 120 | NULL | +----+---------------------+------+------------+-------+-------+-------+--------+------+------+-------+---------+

Hi Geoffrey, You need to change the data format for the Time data type in Tools->Tool Properties, in the General/Data Formats category. Please see the Users Guide for details: http://confluence.dbvis.com/display/UG91/Changing+the+Data+Display+Format Best Regards, Hans

Hans: Thank you for your reply. I tried your fix and this is what happens. Let's start with what I get at the command line: select etime, ap from erg where id=1; +------------+------------+ | etime | ap | +------------+------------+ | 00:05:06.7 | 00:02:33.4 | +------------+------------+ That info is correct. If I change the time format to HH:mm:ss.S, what displays in DbVisualiser is: 00:05.06.0 and 00:02:33.0 So until this gets resolved, I think I should use the command line (and not DbVisualiser) to view/edit this particular table. Also, just because I want tenths of seconds in these two columns in this table, that doesn't mean I want to see tenths of seconds for all time values everywhere, so the Preferences option, when it will work (future), seems too much one-size-fits-all. I guess I was hoping that DbVisualiser would "see" that the column specification was TIME(1), and automagically display (and also not zero) the tenths of seconds. Edited by: Geoffrey Knauth on Jul 8, 2014 4:20 PM

Hi Geoffrey, I installed the same version of the MySQL database as you use and tested with the latest JDBC driver, and I can reproduce the problem. Unfortunately, it seems like it is the driver that does not handle fractional seconds well on TIME columns. For instance, ResultSet#getTime() strips off the fractions and ResultSet#getString() gives an error about bad format for the TIME value. I don't see how we can work around those issues so I suggest that you contact the MySQL Connector/J community and ask them to look into this. The support for fractional seconds in a TIMESTAMP column seems to be in better shape, though. With a TIMESTAMP column, I can update the fractional seconds both in the Data tab and with an UPDATE statement in the SQL Commander, and it is displayed correctly after the update. Is an option for you to use TIMESTAMP columns instead? Best Regards, Hans