Apache POI doesn't correctly read seconds from Excel cells

I am currently debugging someone elses code base. The aim is to import data from Excel files into a database. Each row in the excel file contains a timestamp in column 0 and some label-values in the further columns.

The timestamps contain year, month, day_of_month, hour, minute, and second. To parse the excel files and read individual cells, the following APIs and code is used:

import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

...

// some loop

   LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue();

The parsing of dates works properly in most cases, but I found out, that this doesn't hold for all cases. The problem is, that Java sometimes extends the seconds of a timestamp by nanoseconds. For example, the timestamp "12.09.2018 12:39:11" is interpreted by Java as "2018-09-12T12:39:10.995". The problem is directly translated into the db: second 11 from the file is being saved as second 10 in the db (so the nanosecond-artifact is gone).

To understand the cause of the problem, I read the following documentation: https://github.com/apache/poi/blob/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java

From this I concluded, that the cause of my problem is a rounding error: the API interprets each date value as double. In Excel you can see this double value by converting a date time value e.g. into text or numeric; e.g. "20.03.2019 08:36:39" becomes "43544,3587847222". Hence, due to the datatype for certain values the rounding simply is inaccurate and therefore Java doesn't correctly parse the value.

My question is, how the quickes and accurate fix for such a problem could look like. Basically, I thought about 2 possibilities:

1. Instead of using the Apache POI I could parse the dates through a DateFormatter instead. This, however, has the disadvantage, that user-defined dates always need to be of the same format, which they are currently not.
2. In the code, I could try to round to correct second values and cut of the nanosecond-artifacts. However, I am not sure, if I always need to round to the next second-value above (like in the above shown example) or if there are cases (e.g. nanosecond-values below 0.5) in which I need to round to the next second-value below. My problem here is, that I don't fully understand the exact system behind the programs behavior with regard to the rounding error.

Does someone have a suggestion? Help is very much appreciated!

Edit:
The cause of the problem was, that the nanoseconds were already in the Excel-sheets, but not recognizable for the bare eye, because the related datatype didn't display them.

I cannot reproduce the issue. If apache poi gets LocalDateTime 2018-09-12T12:39:10.995 then the Excel cell has stored that exact date time. Of curse Excel might not show it exactly because of a date format which rounds already. For example date format DD.MM.YYYY hh:mm:ss will show 12.09.2018 12:39:11 for 2018-09-12T12:39:10.995. But stored is the exact date time.

But if the need is getting the LocalDateTime only in accuracy of seconds, one could add 0.5 seconds (500 thousandths of a second) and then truncate to seconds. That approach will round the LocalDateTime to seconds.

LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue(); //got directly from Excel
dateCellValue  = dateCellValue.plusNanos(500000000).truncatedTo(ChronoUnit.SECONDS); //round to seconds

Complete example:

Excel sheet looks like:

Cell values in column B here are date time values. Cell number format is TT.MM.YYYY hh:mm:ss.000.

Code:

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.io.FileInputStream;
class ExcelReadLocalDateTime {
public static void main(String[] args) throws Exception {
//Workbook workbook = WorkbookFactory.create(new FileInputStream("Workbook.xls")); String filePath = "WorkbookNew.xls";
Workbook workbook = WorkbookFactory.create(new FileInputStream("Workbook.xlsx")); String filePath = "WorkbookNew.xlsx";
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue(); //got directly from Excel
System.out.println(dateCellValue);
dateCellValue  = dateCellValue.plusNanos(500000000).truncatedTo(ChronoUnit.SECONDS); //round to seconds
System.out.println(dateCellValue);
} else {
System.out.println(cell.getNumericCellValue());
}
break;    
default:
System.out.println();
}
}
}
workbook.close();
}
}

Result:

Text
DateTime
DT 1
2018-09-12T12:39:10
2018-09-12T12:39:10
DT 2
2018-09-12T12:39:10.123
2018-09-12T12:39:10
DT 3
2018-09-12T12:39:10.245
2018-09-12T12:39:10
DT 4
2018-09-12T12:39:10.370
2018-09-12T12:39:10
DT 5
2018-09-12T12:39:10.495
2018-09-12T12:39:10
DT 6
2018-09-12T12:39:10.500
2018-09-12T12:39:11
DT 7
2018-09-12T12:39:10.620
2018-09-12T12:39:11
DT 8
2018-09-12T12:39:10.745
2018-09-12T12:39:11
DT 9
2018-09-12T12:39:10.870
2018-09-12T12:39:11
DT 10
2018-09-12T12:39:10.995
2018-09-12T12:39:11