[drill] branch master updated: DRILL-8071: Use Excel cell.getLocalDateTimeCellValue (#2400)

[cgivre]

This is an automated email from the ASF dual-hosted git repository.

cgivre pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git


The following commit(s) were added to refs/heads/master by this push:
     new 0b50737  DRILL-8071: Use Excel cell.getLocalDateTimeCellValue (#2400)
0b50737 is described below

commit 0b507378c1b35754cbd11c8a44f1d93d9a045a8e
Author: PJ Fanning <pjfann...@users.noreply.github.com>
AuthorDate: Mon Dec 13 15:12:25 2021 +0100

    DRILL-8071: Use Excel cell.getLocalDateTimeCellValue (#2400)
    
    * DRILL-8071: use excel cell.getLocalDateTimeCellValue
    
    * Update pom.xml
---
 .../drill/exec/store/excel/ExcelBatchReader.java   |  14 +++++++----
 .../drill/exec/store/excel/TestExcelFormat.java    |  26 +++++++++++++++++++++
 .../src/test/resources/excel/1904Dates.xlsx        | Bin 0 -> 6405 bytes
 3 files changed, 35 insertions(+), 5 deletions(-)

diff --git 
a/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
 
b/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
index 26e925f..feabf3c 100644
--- 
a/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
+++ 
b/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
@@ -50,6 +50,8 @@ import org.slf4j.LoggerFactory;
 import java.io.IOException;
 import java.io.InputStream;
 import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.ZoneOffset;
 import java.util.ArrayList;
 import java.util.Date;
 import java.util.HashMap;
@@ -57,7 +59,6 @@ import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 import java.util.NoSuchElementException;
-import java.util.TimeZone;
 
 public class ExcelBatchReader implements ManagedReader<FileSchemaNegotiator> {
 
@@ -846,10 +847,13 @@ public class ExcelBatchReader implements 
ManagedReader<FileSchemaNegotiator> {
       if (cell == null) {
         columnWriter.setNull();
       } else {
-        logger.debug("Cell value: {}", cell.getNumericCellValue());
-        Date dt = DateUtil.getJavaDate(cell.getNumericCellValue(), 
TimeZone.getTimeZone("UTC"));
-        Instant timeStamp = 
Instant.ofEpochMilli(dt.toInstant().getEpochSecond() * 1000);
-        columnWriter.setTimestamp(timeStamp);
+        LocalDateTime dt = cell.getLocalDateTimeCellValue();
+        logger.debug("Cell value: {}", dt);
+        if (dt == null) {
+          columnWriter.setNull();
+        } else {
+          columnWriter.setTimestamp(dt.toInstant(ZoneOffset.UTC));
+        }
       }
     }
   }
diff --git 
a/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
 
b/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
index 1a65512..a8caba1 100644
--- 
a/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
+++ 
b/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
@@ -36,6 +36,8 @@ import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
 import java.nio.file.Paths;
+import java.time.LocalDate;
+import java.time.ZoneOffset;
 
 import static org.apache.drill.test.QueryTestUtil.ConvertDateToLong;
 import static org.apache.drill.test.QueryTestUtil.generateCompressedFile;
@@ -678,4 +680,28 @@ public class TestExcelFormat extends ClusterTest {
 
     new RowSetComparison(expected).verifyAndClearAll(results);
   }
+
+  @Test
+  public void test1904BasedDates() throws RpcException {
+    String sql = "SELECT * FROM dfs.`excel/1904Dates.xlsx`";
+
+    RowSet results = client.queryBuilder().sql(sql).rowSet();
+    TupleMetadata expectedSchema = new SchemaBuilder()
+      .addNullable("playerId", MinorType.VARCHAR)
+      .addNullable("birthYear", MinorType.FLOAT8)
+      .addNullable("birthMonth", MinorType.FLOAT8)
+      .addNullable("birthDay", MinorType.FLOAT8)
+      .addNullable("known", MinorType.FLOAT8)
+      .addNullable("date", MinorType.TIMESTAMP)
+      .buildSchema();
+
+    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema)
+      .addRow("foo", 1991, 10, 14, 1, 
LocalDate.parse("1991-10-14").atStartOfDay().toInstant(ZoneOffset.UTC))
+      .addRow("bar", 1989, 12, 16, 1, 
LocalDate.parse("1989-12-16").atStartOfDay().toInstant(ZoneOffset.UTC))
+      .addRow("baz", 1994, 3, 10, 0, 
LocalDate.parse("1994-03-10").atStartOfDay().toInstant(ZoneOffset.UTC))
+      .build();
+
+    new RowSetComparison(expected).verifyAndClearAll(results);
+  }
+
 }
diff --git a/contrib/format-excel/src/test/resources/excel/1904Dates.xlsx 
b/contrib/format-excel/src/test/resources/excel/1904Dates.xlsx
new file mode 100644
index 0000000..06e1519
Binary files /dev/null and 
b/contrib/format-excel/src/test/resources/excel/1904Dates.xlsx differ