title: why is spring data jpa hibernate mariadb timestamp fractional seconds truncated on insert?
body: <p>I have a column with the following definition:</p>
  
  <pre><code>@Column(columnDefinition="DATETIME(3)")
  private Timestamp timestamp;
  public Timestamp getTimestamp() {return this.timestamp;}
  public void setTimestamp(final Timestamp timestamp) {this.timestamp = timestamp;}
  public String getFormattedTimestamp() {return Util.getFormattedTimestamp(this.timestamp);}
  </code></pre>
  
  <p>If I save the grand-parent entity (it's in a @ManyToOne relationship in another @ManyToOne relationship), the fractional seconds are always set to .000, however, if I manually insert into the table, the fractional seconds are properly retained.</p>
  
  <p>The resultant table definitions is as follows:</p>
  
  <pre><code>MariaDB [field_server]&gt; show columns from device_timestamp;
  +-----------------------+--------------+------+-----+---------+-------+
  | Field                 | Type         | Null | Key | Default | Extra |
  +-----------------------+--------------+------+-----+---------+-------+
  | id                    | bigint(20)   | NO   | PRI | NULL    |       |
  | deviceTimestampReason | int(11)      | YES  |     | NULL    |       |
  | loggedIn              | bit(1)       | NO   |     | NULL    |       |
  | reason                | varchar(255) | YES  |     | NULL    |       |
  | timestamp             | datetime(3)  | YES  |     | NULL    |       |
  +-----------------------+--------------+------+-----+---------+-------+
  5 rows in set (0.00 sec)
  
  MariaDB [field_server]&gt;
  </code></pre>
  
  <p>Program trace indicates the following:</p>
  
  <pre><code>webadmin - 2017-05-23 08:20:01,394 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:01,394 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@2aef651: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:01,394 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp#8]
  webadmin - 2017-05-23 08:20:01,395 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [0] to parameter: [1]
  webadmin - 2017-05-23 08:20:01,395 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [true]
  webadmin - 2017-05-23 08:20:01,395 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:01,396 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2017-05-23 08:20:01.176]
  webadmin - 2017-05-23 08:20:01,396 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [BIGINT] - [8]
  
  
  webadmin - 2017-05-23 08:20:01,404 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:01,404 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@67f8586f: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:01,405 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp#9]
  webadmin - 2017-05-23 08:20:01,405 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [1] to parameter: [1]
  webadmin - 2017-05-23 08:20:01,405 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [false]
  webadmin - 2017-05-23 08:20:01,406 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:01,406 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2017-05-23 08:20:03.176]
  webadmin - 2017-05-23 08:20:01,406 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [BIGINT] - [9]
  
  
  webadmin - 2017-05-23 08:20:01,622 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@42a057b6: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp#12]
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [0] to parameter: [1]
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [true]
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:01,623 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2017-05-23 08:20:01.455]
  webadmin - 2017-05-23 08:20:01,624 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [BIGINT] - [12]
  
  
  webadmin - 2017-05-23 08:20:01,625 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:01,625 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@62036b05: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:01,625 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp#13]
  webadmin - 2017-05-23 08:20:01,626 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [1] to parameter: [1]
  webadmin - 2017-05-23 08:20:01,626 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [false]
  webadmin - 2017-05-23 08:20:01,626 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:01,626 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2017-05-23 08:20:03.455]
  webadmin - 2017-05-23 08:20:01,626 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [BIGINT] - [13]
  
  
  webadmin - 2017-05-23 08:20:02,419 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:02,419 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@126320a8: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:02,419 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp_AUD#component[id,REV]{REV=org.hibernate.envers.DefaultRevisionEntity#1, id=8}]
  webadmin - 2017-05-23 08:20:02,419 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [0]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [0] to parameter: [2]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BOOLEAN] - [true]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [TIMESTAMP] - [2017-05-23 08:20:01.176]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [6] as [BIGINT] - [8]
  webadmin - 2017-05-23 08:20:02,420 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [7] as [INTEGER] - [1]
  
  
  webadmin - 2017-05-23 08:20:02,421 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@12b0440b: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp_AUD#component[id,REV]{REV=org.hibernate.envers.DefaultRevisionEntity#1, id=9}]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [0]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [1] to parameter: [2]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BOOLEAN] - [false]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [TIMESTAMP] - [2017-05-23 08:20:03.176]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [6] as [BIGINT] - [9]
  webadmin - 2017-05-23 08:20:02,422 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [7] as [INTEGER] - [1]
  
  
  webadmin - 2017-05-23 08:20:02,439 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@76737130: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp_AUD#component[id,REV]{REV=org.hibernate.envers.DefaultRevisionEntity#1, id=12}]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [0]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [0] to parameter: [2]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BOOLEAN] - [true]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:02,440 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [TIMESTAMP] - [2017-05-23 08:20:01.455]
  webadmin - 2017-05-23 08:20:02,441 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [6] as [BIGINT] - [12]
  webadmin - 2017-05-23 08:20:02,441 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [7] as [INTEGER] - [1]
  
  
  webadmin - 2017-05-23 08:20:02,442 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (?, ?, ?, ?, ?, ?, ?)
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Registering statement [com.mysql.jdbc.JDBC42PreparedStatement@6f2a13c7: insert into device_timestamp_AUD (REVTYPE, deviceTimestampReason, loggedIn, reason, timestamp, id, REV) values (** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **, ** NOT SPECIFIED **)]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [ws.daley.field.persistence.model.DeviceTimestamp_AUD#component[id,REV]{REV=org.hibernate.envers.DefaultRevisionEntity#1, id=13}]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [0]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.type.EnumType$EnumValueMapperSupport - Binding [1] to parameter: [2]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BOOLEAN] - [false]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [setupDataLoader]
  webadmin - 2017-05-23 08:20:02,442 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [5] as [TIMESTAMP] - [2017-05-23 08:20:03.455]
  webadmin - 2017-05-23 08:20:02,443 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [6] as [BIGINT] - [13]
  webadmin - 2017-05-23 08:20:02,443 [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [7] as [INTEGER] - [1]
  </code></pre>
  
  <p>Note that the data appears to be inserted with the fractional seconds in tact. However, the following shows that no fractional seconds are sent to the database.</p>
  
  <pre><code>MariaDB [field_server]&gt; select * from device_timestamp;
  +----+-----------------------+----------+-----------------+-------------------------+
  | id | deviceTimestampReason | loggedIn | reason          | timestamp               |
  +----+-----------------------+----------+-----------------+-------------------------+
  |  8 |                     0 |         | setupDataLoader | 2017-05-23 08:20:01.000 |
  |  9 |                     1 |          | setupDataLoader | 2017-05-23 08:20:03.000 |
  | 12 |                     0 |         | setupDataLoader | 2017-05-23 08:20:01.000 |
  | 13 |                     1 |          | setupDataLoader | 2017-05-23 08:20:03.000 |
  +----+-----------------------+----------+-----------------+-------------------------+
  4 rows in set (0.00 sec)
  
  MariaDB [field_server]&gt;
  </code></pre>
  
  <p>I have included useFractionalSeconds in my jdbc.url:</p>
  
  <pre><code>jdbc.url=jdbc:mysql://localhost:3306/field_server?createDatabaseIfNotExist=true&amp;useFractionalSeconds=true
  </code></pre>
  
  <p>And here's the SQL. No fractional seconds:</p>
  
  <pre><code>webadmin - 2017-05-23 19:17:05,005 [main] DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  Hibernate: insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (?, ?, ?, ?, ?)
  /* conn id 1046 clock: 1495581425008 */ insert into device_timestamp (deviceTimestampReason, loggedIn, reason, timestamp, id) values (0, 1, 'test', '2017-05-23 19:17:00', 77);
  webadmin - 2017-05-23 19:17:05,008 [main] TRACE com.mysql.jdbc.log.Slf4JLogger - send() packet payload:
  host: 'localhost' threadId: '1046'
  87 00 00 00 03 69 6e 73     . . . . . i n s 
  65 72 74 20 69 6e 74 6f     e r t . i n t o 
  20 64 65 76 69 63 65 5f     . d e v i c e _ 
  74 69 6d 65 73 74 61 6d     t i m e s t a m 
  70 20 28 64 65 76 69 63     p . ( d e v i c 
  65 54 69 6d 65 73 74 61     e T i m e s t a 
  6d 70 52 65 61 73 6f 6e     m p R e a s o n 
  2c 20 6c 6f 67 67 65 64     , . l o g g e d 
  49 6e 2c 20 72 65 61 73     I n , . r e a s 
  6f 6e 2c 20 74 69 6d 65     o n , . t i m e 
  73 74 61 6d 70 2c 20 69     s t a m p , . i 
  64 29 20 76 61 6c 75 65     d ) . v a l u e 
  73 20 28 30 2c 20 31 2c     s . ( 0 , . 1 , 
  20 27 74 65 73 74 27 2c     . ' t e s t ' , 
  20 27 32 30 31 37 2d 30     . ' 2 0 1 7 - 0 
  35 2d 32 33 20 31 39 3a     5 - 2 3 . 1 9 : 
  31 37 3a 30 30 27 2c 20     1 7 : 0 0 ' , . 
  37 37 29                    7 7 ) 
  </code></pre>