I have a Java program which uses Spring JPA that logs events to a MySQL table.
In the entity class, I have defined the timestamp attribute as below:
@Column("received_timestamp")
private Instant receivedTimestamp;
The datatype of received_timestamp is TIMESTAMP.
Before inserting into DB I am converting the timestamp in the event I receive from EST to an instance of java.time.Instant. When I debug, I can see that, this converts the received time to UTC.
Events received between 09PM and 10PM on the 09th of March 2024, have failed with the error Incorrect datetime value.
My MySQL server is in the timezone EST/EDT.
My theory is that although I sent UTC timestamps to MySQL, it assumed that I am sending timestamps in EST and tried to convert it to UTC. E.g.: an event received at 09:30PM EST would be converted to 02:30AM by my application. MySQL, thinking 02:30AM is in EDT, tries to convert it to UTC but it fails because 02:30AM EDT is not a valid time.
Could someone please validate this theory.