0

I have a Play Framework application with ebeans.To synchronise between the main system and an offline system I use an addDate field to identify unique records.

This works perfect while developing and initial testing (development with jdbc:h2:mem:play;MODE=MYSQL) and testing with a limited dataset.

But in production, with concurrent users I discovered that in the MySQL database milliseconds are not stored. I have the fields created as datetime(6), but a query like:

SELECT UNIX_TIMESTAMP(add_date) FROM `user` WHERE 1

returns:

1499722493.000000
1499772800.000000
1499777225.000000
1499790922.000000
1499875868.000000
1499954855.000000
1499977124.000000
1499981148.000000
1499986822.000000

(ps. just selecting the add_date field in MySQL return 2015-12-17 16:15:50.000000, showing the precision is there)

So milliseconds are not stored. In Java I use the normal Date class, so they are in there (and so in the H2 men test database).

protected Date addDate;

How can I have the milliseconds stored in the Database with my current configuration without, hopefully, having to rewrite too much code?

[Edit:]

I am using MySQL on Debian: Ver 14.14 Distrib 5.6.35
sbt.version=0.13.11
addSbtPlugin("com.typesafe.play" % "sbt-plugin" % "2.5.16")
addSbtPlugin("com.typesafe.sbt" % "sbt-play-ebean" % "3.0.2")
mysql-connector-java % 5.1.43
Luuk D. Jansen
  • 4,402
  • 8
  • 47
  • 90
  • what this have to Play Framework? – Jacek Cz Aug 12 '17 at 09:54
  • It is a Play Framework application. Somewhere between the ebeans implementation and the MySql database the milliseconds are not stored. So it seems that it is a Play Framework question. Do I need to add a annotation to have the implementation treat it differently, or define the field differently? Why does it work with the H2 men and not with MySQL? – Luuk D. Jansen Aug 12 '17 at 09:58
  • What version of MySQL? – Strawberry Aug 12 '17 at 10:43
  • MySQL version 14.14. – Luuk D. Jansen Aug 12 '17 at 10:49
  • Sorry, Ver 14.14 Distrib 5.6.35. There is no problem with fractions/microseconds on the database, I tested that. I need to figure how to have this precision saved from my Play application to the database. – Luuk D. Jansen Aug 12 '17 at 11:16
  • java.util.Date class doesnt work with milliseconds. i think you need to write your own class... or use this class http://joda-time.sourceforge.net/apidocs/org/joda/time/DateTime.html – Raymond Nijland Aug 12 '17 at 12:15
  • @RaymondNijland Both `java.util.Date` and Joda-Time are now supplanted by the [java.time](https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html) classes. – Basil Bourque Aug 13 '17 at 04:02
  • @RaymondNijland `java.util.Date` does indeed work with milliseconds. First line of [class doc](https://docs.oracle.com/javase/8/docs/api/java/util/Date.html): *The class Date represents a specific instant in time, with millisecond precision.* – Basil Bourque Aug 13 '17 at 04:13

2 Answers2

1

java.time

The troublesome java.util.Date class is now supplanted by the java.time classes.

The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

Capturing the current moment is limited to milliseconds in Java 8 but expanded to up to nanoseconds in Java 9 and later with a new implementation of Clock. To be clear, I'll repeat: Both Java 8 and Java 9 can store a value with nanoseconds, but only Java 9 and later can capture the current moment in finer than milliseconds resolution.

Instant instant = Instant.now() ;  // Captures milliseconds in Java 8 but nanoseconds in Java 9+.

Use a JDBC driver that complies with JDBC 4.2 or later, to work directly with the java.time types rather than the hacked java.sql types. Call the PreparedStatement::setObject and ResultSet::getObject methods.

myPreparedStatement.setObject( … , instant ) ;

…and…

Instant instant = myResultSet.getObject( … , Instant.class ) ; // Transfers a value with nanoseconds if present.

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.

Where to obtain the java.time classes?

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
0

In java you can not accurate to the microsecond (nanosecond) without taking a lot of system resources. Your date is inserted in milliseconds because java does it in milliseconds.

If you want to use microseconds from SQL you must use the SQL NOW() on insertion.

tronic
  • 459
  • 2
  • 11
  • Thanks, milliseconds if fine with me, second is just not accurate enough. But how do I get that to work? As Play/eBeans seems to work with the h2/mem option, is it the Connector/J interface? I created my tables with datetime(6). – Luuk D. Jansen Aug 12 '17 at 13:01
  • Sorry. I don't know, use NOW() if you can, the rest is a waste of time. Also an unique ID on the date is a big mistake. – tronic Aug 12 '17 at 13:09
  • Java.sql.Timestamp instead of java.util.Date maybe, with TIMESTAMP mysql type – tronic Aug 12 '17 at 13:16
  • I tried TIMESTAMP on the java code, but it didn't make a difference. I don't use the date as the unique id, but use it as a reference between two independent systems as the relevant items could have different ids. Maybe this will be solved in a future release as it is only since MySQL 5.6 that this is possible and works for H2. – Luuk D. Jansen Aug 12 '17 at 16:48
  • What do mean by that first sentence? Can you cite documentation? – Basil Bourque Aug 13 '17 at 04:15