SimpleDateFormat not parsing milliseconds and MySql is rounding Date

Question

I'm using SimpleDateFormat to parse string to Date. After the parse, I'm loading this date to mysql. The problem is that milliseconds is not being parsed to Date and when I insert the object on Mysql its getting rounded.

myStringDate = "2018-02-02 23:59:59.700"
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss.SSS");
myObject.setDate(fmt.parse(myStringDate );

When I debug the code I can see the Date as Sun Feb 02 23:59:59 BRST 2018 (its not storing milliseconds)

The data is stored on mysql as '2017-02-03 00:00:00'

My model is mapped as :

import javax.persistence.*;
import java.util.Date;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "date")
private Date date;

And column on MySqls is TYPE DATETIME.

Answer 1

I used LocalDateTime:

@Column(name = "date", columnDefinition = "DATETIME(3)") private LocalDateTime date;

Answer 2

The date format in SQL is YY-MM-dd hh24:mi:ss:mmm while in Java is YY-MM-dd HH:mm:ss.SSS

Answer 3

tl;dr

myPreparedStatement.setObject(
    … ,
    LocalDateTime.parse( 
        "2017-02-03 00:00:00".replace( " " , "T" ) 
    )
)

Smart types, not dumb strings

The data is stored on mysql as '2017-02-03 00:00:00'

No, the data is not stored that way. You said:

column on MySqls is TYPE DATETIME.

That means the date-time is not stored as text, and does not have a “format”.

As the documentation says:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.

Key words there are: retrieves and displays. After extracting the date-time value, some text is constructed to represent that value. Do not conflate the textual representation of the date-time value with the date-time value. Both your database and Java have their own internally-defined way of storing a date-time, but neither use plain text. Both your database and Java can generate text to display a date-time value, as needed.

Unzoned

I am a Postgres user, not MySQL, but it seems that its DATETIME type is akin to the SQL-standard TIMESTAMP WITHOUT TIME ZONE type. This means it does not represent an actual moment, is not a point on the timeline. It represents potential moments along a range of about 26-27 hours. Without the context of a time zone or offset-from-UTC, it has no real meaning.

java.time classes

Avoid the legacy date-time types. Use only java.time classes instead. According to this Question, JPA & Hibernate now support the java.time types.

If you do have input text such as 2018-02-02 23:59:59.700, parse as a LocalDateTime, given the lack of zone/offset information. Like the MySQL type DATETIME and the SQL type TIMESTAMP WITHOUT TIME ZONE, this class lacks any concept of zone/offset. To parse, convert from SQL-style with a SPACE in the middle to ISO 8601 standard format with a T in the middle.

String input = "2018-02-02 23:59:59.700".replace( " " , "T" ) ;

The java.time classes use ISO 8601 formats by default when parsing/generating strings. So no need to define a formatting pattern.

LocalDateDate ldt = LocalDateTime.parse( input ) ;

No need for strings when communicating with your database. As of JDBC 4.2 and later, you can directly exchange java.time objects. (Or let JPA/Hibernate do so under-the-covers.)

myPreparedStatement.setObject( … , ldt ) ;

And retrieval.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

FYI, if you are actually trying to track specific moments in time, you are using the wrong types. You should be using TIMESTAMP in MySQL or TIMESTAMP WITH TIME ZONE in standard SQL, and in Java use Instant and ZonedDateTime.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 4

The pattern for month of the year should be MM (uppercase) not mm (lowercase) for SimpleDateFormat. It is possible your code is reading the month as a minutes value.

Answer 5

I think the milliseconds are actually being parsed, you just aren't seeing them with the default formatting that you're getting when you're displaying the date for debugging purposes.

Using this code:

SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
String myStringDate = "2018-02-02 23:59:59.700";
Date date = fmt.parse(myStringDate);
System.out.println(date);
System.out.println(fmt.format(date));

I get this output:

Fri Feb 02 23:59:59 EST 2018
2018-02-02 23:59:59.700

Update: Make sure you use capital MM for the month, not mm.

Answer 6

If you are using MySQL's version 5.6.4, you can see this happen by declaring columns with fractional-second time datatypes.

This documentation shows more in detail about this feature, and as far as a quick check is concerned - you may try SELECT NOW(3) should give you the present time from your MySQL server's operating system with millisecond precision and instead if you are getting an error then you are not using the right version.

Alongside to that, moving from the older Connector/J driver to mysql-connector-java-5.1.26.jar is very much suggested.

Hope this helps answer your question!