AixNPanes
Reputation: 1260
why is spring data jpa hibernate mariadb timestamp fractional seconds truncated on insert?
I have a column with the following definition:
@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);}
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.
The resultant table definitions is as follows:
MariaDB [field_server]> 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]>
Program trace indicates the following:
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]
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.
MariaDB [field_server]> 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]>
I have included useFractionalSeconds in my jdbc.url:
jdbc.url=jdbc:mysql://localhost:3306/field_server?createDatabaseIfNotExist=true&useFractionalSeconds=true
And here's the SQL. No fractional seconds:
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 )
Upvotes: 2
Views: 1661
Answers (2)
AixNPanes
Reputation: 1260
By switching from the MySQL connector to the MariaDB connector, the problem was solved. As I remember, in the earlier days of MariaDB there was no MariaDB connector and you had to use the MySQL connector. Apparently that is no longer the case. If you're using MariaDB, then it looks like the MariaDB connector is required.
Upvotes: 4
Rick James
Reputation: 142298
I suspect the problem goes back to the source of the data.
mysql> SELECT NOW(), NOW(3);
+---------------------+-------------------------+
| NOW() | NOW(3) |
+---------------------+-------------------------+
| 2017-05-23 07:39:29 | 2017-05-23 07:39:29.110 |
+---------------------+-------------------------+
My point is, if the source does not have milliseconds, then .000 is supplied during the insert.
Also, why generate the time in python? As you see, MySQL is happy to generate the times (to up to 6 decimal places).
If you need a uniform time for multiple SQL statements, then you can capture it once, then reuse:
mysql> SELECT @ts := NOW(6);
+----------------------------+
| @ts := NOW(6) |
+----------------------------+
| 2017-05-23 07:43:35.584183 |
+----------------------------+
mysql> SELECT @ts, NOW(6);
+----------------------------+----------------------------+
| @ts | NOW(6) |
+----------------------------+----------------------------+
| 2017-05-23 07:43:35.584183 | 2017-05-23 07:43:53.821409 |
+----------------------------+----------------------------+
mysql> SELECT @ts, NOW(6);
+----------------------------+----------------------------+
| @ts | NOW(6) |
+----------------------------+----------------------------+
| 2017-05-23 07:43:35.584183 | 2017-05-23 07:43:55.570200 |
+----------------------------+----------------------------+
Caveat: I think version 5.6.4 is when fractional seconds was first fully implemented.
Closer test
Using 10.0.28:
mysql> create table now3 (dt datetime(3), ts timestamp(3));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into now3 (dt, ts)
values (now(), now()),
(curdate(), curdate()),
(now(3), now(3));
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into now3 (dt, ts)
values ('2017-05-23 19:19:13.123',
'2017-05-23 19:19:13.123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from now3;
+-------------------------+-------------------------+
| dt | ts |
+-------------------------+-------------------------+
| 2017-05-23 19:19:13.000 | 2017-05-23 19:19:13.000 | -- NOW() has mo ms
| 2017-05-23 00:00:00.000 | 2017-05-23 00:00:00.000 | -- CURDATE() has no time
| 2017-05-23 19:19:13.842 | 2017-05-23 19:19:13.842 | -- NOW(3) worked
| 2017-05-23 19:19:13.123 | 2017-05-23 19:19:13.123 | -- from string
+-------------------------+-------------------------+
mysql> select @@version;
+--------------------------+
| @@version |
+--------------------------+
| 10.0.28-MariaDB-1~jessie |
+--------------------------+
So... I guess that the data coming in is a problem. Can you dump the actual SQL?
Upvotes: 0
Related Questions
- Timestamp Time is changed after fetching the record
- Spring Boot JPA Hibernate - Storing Date with millisecond precision
- Local date time in entity ignoring last 3 digits of given format on save
- Java Spring - MySQL timestamp field and millisecond precision
- Hibernate timestamp - millisecond precision
- 12:00:00 stored as 00:00:00 in MySql when TemporalType.Time is used via Hibernate
- Hibernate Incorrect datetime value
- Properly Utilizing Epoch Seconds with JPA in Spring Boot
- Hibernate date don't save the milliseconds
- Java hibernate truncates time part from MySQL datetime while reading