prevent rounding off of timestamp in mysql

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.

[Posting to save time for others]

I faced a similar problem with mysql/aurora.

I was trying to use new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(createdOn) in the queries, but found out that SimpleDateFormat simply drops additional fields.

Hibernate's default mapping of Date fields to MySQL DateTime columns (using the annotation @Temporal(TemporalType.TIMESTAMP)) was rounding off the milliseconds.

In order to generate consistent queries i had to implement rounding off myself while using SimpleDateFormat.

Example round off code:

Calendar cal = Calendar.getInstance();cal.setTime(this.getCreatedOn());if (cal.get(Calendar.MILLISECOND) >= 500 ) {  System.out.println("Round off milliseconds to seconds");  cal.set(Calendar.SECOND, cal.get(Calendar.SECOND) + 1);}

Usage in queries:

createdOn = "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(roundedCreatedOn)+ "'"