Timestamp precision lost in Ebean Expressions

[M. Hertig]

Hello there,

I've discovered some unexpected behavior recently on one of our data synchronization API endpoints.

We use a MySQL database and the Timestamp fields are defined with millisecond precision TIMESTAMP(3).

Problem

Let's assume we have a Timestamp of "2020-05-13 09:25:00.253" in the "updated_at" field on the database.

Querying the database with a Finder as follows will still return this entry, even though it should not.

dummy code

String format = "yyyy-MM-dd'T'HH:mm:ss.SSS"
DateFormat formatter = new SimpleDateFormat(format);

Date parsedDate = formatter.parse("2020-05-13T09:25:00.520");

Timestamp timestamp = new Timestamp(parsedDate.getTime());

find.query().where().gt("updated_at", timestamp).findList();

It seems that the millisecond precision is lost, when Ebean converts the timestamp passed into the gt() method back to a string internally (?).

We fixed the issue by doing the Timestamp-To-String conversion ourselves and pass the timestamp as String (with millisecond precision) into the gt() method.

Question

Is this a bug in Ebean or is this expected behavior?

Do I have to configure something in Ebean so the timestamps are converted correctly?

Hint

I found out about this issue when I logged the SQL Queries, and executed them directly against the MySQL database. Which got me different results.

select t0.id, t0.updated_at from example where t0.updated_at > ?; --bind (2020-05-13 09:25:00.520)

The timestamp was displayed with millisecond precision in the bind(...) part, but it still returned timestamps in the same second with lower milliseconds.

Versions

Sbt-Plugin

addSbtPlugin("com.typesafe.sbt" % "sbt-play-ebean" % "5.0.0")

Playframework

addSbtPlugin("com.typesafe.play" % "sbt-plugin" % "2.7.3")

[Rob Bygrave]

> It seems that the millisecond precision is lost

Sounds like a MySql issue to be honest. You don't actually say what your MySql version is so perhaps you don't know that old versions of MySql truncate datetime precision.

I recommend that you create a failing test case that includes your current MySql version. Then update it to latest Ebean, then update to latest MySql. Then you'll probably know where the issue is. 

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/4f296fc8-39b9-4f80-bc0b-a5c1bc0f72dd%40googlegroups.com.

[M. Hertig]

Hi Rob,

Sorry, i should have pointed out that i'm actually on mariadb, maybe theres a problem with that, but to my understanding mariadb/mysql should be interchangeable.

We are using both in our development environment and didn't have any issues with that, until now apparently...

mysql --version:
mysql  Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

And using the MySQL JDBC Connector Version 8.0.20.

What still seems pretty strange to me is that:

• The timestamp on the database has actually millisecond precision (It's explicitly defined as such with TIMESTAMP(3) as datatype)
• Querying the database directly via CLI returns the expected results. (Meaning: "SELECT id, updated_at from example where updated_at > '2020-05-13 09:25:00.520'" does NOT return the row with the timestamp of "2020-05-13 09:25:00.253" -> as you would expect)
• I can confirm that Ebean actually works when passing the Timestamp as String to the greater-than method, BUT passing the exact same representation of the date as java.sql.Timestamp does not.

// this works -> it DOES NOT return the row with an updated_at timestamp of "2020-05-13 09:25:00.253"

find.query().where().gt("updated_at", "2020-05-13 09:25:00.520").findList();

// this doesn't work -> it DOES return the row with an updated_at timestamp of "2020-05-13 09:25:00.253"
find.query().where().gt("updated_at", timestamp).findList(); // timestamp being the java.sql.Timestamp representation of the exact same timestamp as above (with millisecond precision)

I'll report back if I ever find out more about that, but our short term solution is to just pass the Timestamps as String to circumvent that issue.

To unsubscribe from this group and stop receiving emails from it, send an email to eb...@googlegroups.com.