6

Good day,
I create web application service, which would be used across the world.
Therefore I need to store datetime values in UTC and represent them in clocks on the wall time to the end user.
After reading Stack overflow, I understood, that I should:

  1. use timestamp as a column type in DB (currently MariaDB 10.1.20)
  2. use ZonedDateTime in Java (I use java8)

The problem arose while converting between these values.
As I use JDBC, I have to do the following conversion: 

java.sql.Timestamp <-> java.time.ZonedDateTime

My code:

// Get current zonedDateTime
ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneOffset.UTC);

// Convert zonedDateTime to java.sql.Timestamp before saving to DB
Timestamp = Timestamp.from(zonedDateTime.toInstant());

// Get zonedDateTime from resultSet
Timestamp timestamp = (Timestamp) resultSet.getObject("created");
ZonedDateTime zonedDateTime = 
    ZonedDateTime.ofInstant(ts.toInstant(), ZoneOffset.UTC))

When I use:

zonedDateTimeBeforeSave.isEqual(zonedDateTimeAfterSave);

It returns me false (I need to compare them in overridden equal method of my domain model)
Below is print out of both:

zonedDateTimeBeforeSave: 2017-01-24T20:18:17.720Z
zonedDateTimeAfterSave: 2017-01-24T20:18:17Z

Questions:

  1. Am I right in my choice. Perhaps, I should use another column or java type...
  2. Am I right in doing conversions. Maybe there is another better way

Thank you

  1. Edit: After Matt Johnson's help I understood that the problem is in the fact, that when I save datetime to DB, It doesn't save fractions, though It should. FYI column type is timestamp(6).
  2. Edit: Now I use java.time.Instant instead of ZonedDateTime
Sabine
  • 323
  • 4
  • 14
  • 1
    Leaving the fractional second problem aside, why do you not use `Instant` for storing timestamps as UTC? – Meno Hochschild Jan 25 '17 at 08:24
  • I just did it early morning, was in doubts though. You erased them by your question, thank you. I didn't want to edit my answer, as it doesn't affect the fractional behavior and still wanted to see other propositions, concerning the type, like you wrote, for example. – Sabine Jan 25 '17 at 18:39

2 Answers2

2

I finally found out what was the problem after reading this post and comments:

http://mysqlnoob.blogspot.com/2016/09/replication-from-mysql-56-to-mariadb-10-fails-with-fractional-seconds.html

The reason was my MariaDB JDBC driver
I had an old 1.1.7 version and parameter "useFractionalSeconds" was set to false.
The solution is rather setting this parameter to true (f.ex by URL) or update driver. Current latest version is 1.5.7 as for 25.01.2017

Sabine
  • 323
  • 4
  • 14
0

MySQL added support for fractional seconds with version 5.6.4. My guess is you are running something older than that, and therefore your milliseconds are being truncated.

From the MySQL 5.6 docs:

Before MySQL 5.6.4, the instances are limited in which a fractional seconds part is permitted in temporal values. A trailing fractional part is permissible in contexts such as literal values, and in the arguments to or return values from some temporal functions. ... However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. ...

If you like, compare the changes in this doc from versions 5.5, 5.6, and 5.7.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • No, I use MariaDB 10.1.20 (Yes, I was a little bit not exact, concerning my DB) Anyway MariaDB included fractions since 5.3, couple of months before MySQL did. – Sabine Jan 24 '17 at 21:45
  • Oh, ok. Please add that to the question and I'll delete this. – Matt Johnson-Pint Jan 24 '17 at 21:51
  • Don't worry, I appreciate any answer. Moreover, maybe you may help me concerning my choice? Did I do a right one? I mean types? – Sabine Jan 24 '17 at 21:52
  • 1
    `TIMESTAMP` is a fine choice, sure. As long as you only care about persisting the `Instant`. If you want the whole `ZonedDateTime` including time zone, you'll have to save the time zone ID into a separate field. – Matt Johnson-Pint Jan 25 '17 at 20:17
  • Thank you. I've got an idea finally. – Sabine Jan 25 '17 at 20:24
  • No Problem. And yeah, let's leave this answer here too, in case someone with the version issue stumbles across this in the future. Thanks. – Matt Johnson-Pint Jan 25 '17 at 20:28