how to make sure milliseconds of the date are stored in database?

Question

I am using grails 2.2 with mysql.

I get the time as unix timestamp.

def raceStartTime = params.raceStart //milliseconds epoch time

I then convert the time to date as shown below

Date startDateTime = new Date(raceStartTime.toLong())

and persist it in database

Race r = new Race()
r.start = startDateTime 
r.save()

It turns out when saving the date in mysql database the precision is lost i.e the exact value of milliseconds is not recorded. It seems to record time as hh mm ss. I need to preserve the fractional part of the second. Is there a way to make it so that when the timestamp converted date is saved, the fractional second part is not lost? Thanks for help!

UPDATE:

I used the following mapping block to change the datatype.

static mapping = {
    debug type: 'text'
    raceStart sqlType:  'DATETIME(6)'
    raceEnd sqlType:  'DATETIME(6)'
}

After this i run dbm-gorm-diff but it doesnt generate change log. i appreciate any guide.thanks!

Answer 1

This has to do with how you defined your start column in MySQL. From the documentation:

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

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.)

Hence, ensure that you create your table with:

 CREATE TABLE race (... start DATETIME(6) ...)

---

edit: I am no grails expert, but I think you can configure this mapping by defining the following on your Race class:

static mapping = {
   start sqlType: "DATETIME", precision: 6
}