prevent rounding off of timestamp in mysql

Question

I have a MySQL table with a single field of type TIMESTAMP. When I insert a timestamp into the table, it is getting rounded off.

For example - if I do a

insert into tablename (time) values ("2014-08-25 23:59:59.587");

and then I do a

select * from tablename;

I get

2014-08-26 00:00:00

This is causing a bug in my code where I use the timestamp of the last record to determine where a new day has started.

How do I ensure that the fractional part gets stored as it is? (or if it is getting stored correctly, then how do I retrieve it - (using Java))

I am using MySQL 5.6.16 and Java 8 (although the problem does not require java and can be verified from the command line)

When using Java,

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;


public class DBTest {

    public static void main(String[] args) {

           try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection dbConn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/demod?useUnicode=true&characterEncoding=utf8", "dawon","1111");
            Statement stmt = dbConn.createStatement();
            String sql = "SELECT TEST FROM TIME;";
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            Timestamp ts = rs.getTimestamp("TEST");
            System.out.println("time" + ts.toString() + " " + rs.getTimestamp("TEST"));
            //both print out the rounded off value
           } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Answer 1

As of MySQL 5.6.4 the support for fractional seconds is included in Time, DateTime and TimeStamp data types.

Your version should support it, you just need to detail your column as follows:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

You should read the full article since it has more information about it.

Update

It worked for me here using the following command:

/* The fsp value, if given, must be in the range 0 to 6 */
CREATE TABLE tablename (columnname TIMESTAMP(3));

Not a big deal but current version of the Workbench seems to be missing the support for that feature in the "create table" interface. I had to use the direct command to create the table.