I got the following situation. My software operates on a database with tables autogenerated by Microsoft Dynamics NAV 2009. The database runs on an MS SQL Server 2012. We created a new table in NAV that has several datetime fields. NAV stores these to SQL Server using datetime columns (I know this tyoe should not be used today, but NAV 2009 does and I can't change that).
My software receives data from external partners and writes it into this table, so that NAV can read it and work on it. NAV only reads that table, never writes to it.
But for some reason weird things happen when storing the time values to the database. My software is a Spring Boot 5.1 application that uses Hibernate 5.3.7 for ORM. My JPA entity uses the Java 8 type Instant to map the date and time values to the database table.
@Entity
@Table(name = "Nav Company$My Table")
public class MyTableEntity implements Serializable {
/* some more fields here... */
@Column(name = "Our Timestamp", updatable = false, nullable = false)
private Instant ourTimestamp;
@Column(name = "Partner Timestamp", updatable = false, nullable = false)
private Instant partnerTimestamp;
/* getters and setters here */
}
When data from a partner comes in, my software reads the provided timestamp as LocalDateTime, adds timezone information to it to get a ZonedDateTime, extracts the Instant and stores it in the database using the entity.
My logs show that the time calculation in the software is correct. The last line is the Instant that is written to the database.
2019-01-22 09:00:01 [...] : navTimestamp[LDT]: ldt = 2019-01-22T09:00:01.015597300
2019-01-22 09:00:01 [...] : navTimestamp[LDT]: zoneId = Europe/Berlin
2019-01-22 09:00:01 [...] : navTimestamp[LDT]: zdt = 2019-01-22T09:00:01.015597300+01:00[Europe/Berlin]
2019-01-22 09:00:01 [...] : navTimestamp[ZDT]: zdt = 2019-01-22T09:00:01.015597300+01:00[Europe/Berlin]
2019-01-22 09:00:01 [...] : navTimestamp[ZDT]: instant = 2019-01-22T08:00:01.015597300Z
But when I SELECT the data from the table using SQL Server Management Studio, it shows that the database stored 2019-01-22 09:00:01.017 instead of 2019-01-22 08:00:01.017 as I would have expected (remember: column type is datetime, not datetimeoffset). When NAV reads that value, it even adds another hour to convert it into local time (GMT+1), thus showing 2019-01-22 10:00:01.
So I guess somewhere between my application and the SQL Server, Hibernate does some weird things and does not transmit the UTC time stored in the Instant but instead converts it to local time first and then stores it in the database.
How can I make sure that the UTC time is stored in the database and not local time?
