Dates are shifted by 1 day using JDBC Driver

1

For some reason the JDBC driver results are shifting Dates by a day. I’m querying a parquet file.

Dremio CSV download - Matches Dremio UI

2017-09-18T00:00:00.000 397.6321
2017-09-19T00:00:00.000 398.07657
2017-09-20T00:00:00.000 398.32816
2017-09-21T00:00:00.000 397.12952
2017-09-22T00:00:00.000 397.39453

Exact same Dremio SQL Query using JDBC Driver

9/18/2017 398.07657
9/19/2017 398.32816
9/20/2017 397.12952
9/21/2017 397.39453
9/22/2017 397.39453

Running the query with a specific day in JDBC returns back:

where as_of = ‘2017-09-19’

as_of tri_price
9/18/2017 398.07657

2

I checked the data with both Apache Drill and Apache Spark and both returned the back the correct date and price. Looks like a Dremio JDBC specific problem,

Tried:
Spark SQL using SparkR
Drill SQL using Drill UI
Drill SQL using Drill JDBC

and as_of = ‘2017-09-19’

as_of tri_price
9/19/2017 398.07657

3

Hey @david.lee,

can you share which version of the server and the driver you are using, along with the query profile?

4

Build
1.2.2-201710100154510864-d40e31c
Edition
Community Edition
Build Time
10/09/2017 18:56:36
Change Hash
d40e31c9961b60737eb802375c1246264465dc06
Change Time
10/09/2017 18:49:44

JDBC Driver:
dremio-jdbc-driver-1.2.2-201710100154510864-d40e31c.jar

Profile Attached:
c831f4ac-9473-45e6-a879-457650793fcd.zip (3.4 KB)

I tried downgrading to 1.2.1, but it has the same problem… 1.1.0 has the bug that doesn’t read dates properly: -11355-04-22

5

The example above returns back in Dremio UI

SELECT * FROM “temp”.“0_0_0.parquet” where as_of = ‘2009-07-06’ limit 10

{“msr”:“F000002N19”,“as_of”:“2009-07-06”,“tri_price”:25.04}
{“msr”:“F000002OWJ”,“as_of”:“2009-07-06”,“tri_price”:22.646044}
{“msr”:“F000002VFB”,“as_of”:“2009-07-06”,“tri_price”:19.32}
{“msr”:“F000002XFA”,“as_of”:“2009-07-06”,“tri_price”:50.10911}
{“msr”:“F00000J3JR”,“as_of”:“2009-07-06”,“tri_price”:80.568016}
{“msr”:“F00000LVTA”,“as_of”:“2009-07-06”,“tri_price”:35.206165}
{“msr”:“FEUSA00001”,“as_of”:“2009-07-06”,“tri_price”:121.10718}
{“msr”:“FEUSA00002”,“as_of”:“2009-07-06”,“tri_price”:104.68655}
{“msr”:“FEUSA00003”,“as_of”:“2009-07-06”,“tri_price”:36.369663}
{“msr”:“FEUSA00004”,“as_of”:“2009-07-06”,“tri_price”:128.56389}

But the same query using a JDBC BI tool or R (using RJDBC) gives:

msr as_of tri_price
F000002N19 7/5/2009 25.04
F000002OWJ 7/5/2009 22.646044
F000002VFB 7/5/2009 19.32
F000002XFA 7/5/2009 50.10911
F00000J3JR 7/5/2009 80.568016
F00000LVTA 7/5/2009 35.206165
FEUSA00001 7/5/2009 121.10718
FEUSA00002 7/5/2009 104.68655
FEUSA00003 7/5/2009 36.369663
FEUSA00004 7/5/2009 128.56389

6

Thanks @david.lee, we have been able to reproduce the issue and we will be working on a fix.

7

Hey @david.lee,

just to let you know we released Dremio 1.3.0 which contains the fix for this issue.