Dremio Version: 20.1.0-202202061055110045-36733c65
Driver Version: dremio-jdbc-driver-20.1.0-202202061055110045-36733c65.jar
My timezone: ET.
Hi there
I’ve noticed an issue where I’m seeing some weird behavior with Date data types that are returned when using the jdbc driver. It looks like the time portion of the date is dropped, but it is dropped on top of the timezone offset. In the case of the negative timezone offset, that brings us to the previous date. As a result you see 4AM the day before.
Say i have the below query… where today is 2022-09-08 and I queried it at 10:40PM EDT. Note in UTC it would be 2022-09-09 2:40AM ish.
“statement”: “select CURRENT_DATE, CAST(CURRENT_DATE as varchar) as casted_DT , CURRENT_TIMESTAMP as ts from sys.version”,
The output that is returned is the Long version of the Date value returned.
“data”: [
{
“CURRENT_DATE”: 1662609600000,
“casted_DT”: “2022-09-09”,
“ts”: 1662691687873,
}
]
Note that if I take 1662609600000 and convert it to yyyy-mm-dd format, it shows the date as 2022-09-08 4:00AM UTC time. (using online converter to avoid any potential coding error on my side). We were expecting the driver to provide the long value as we see in the ts column. That way, any tz conversions applied on top of it would land into the appropriately. (note it looks like the casted_DT is utc which dremio uses)
To further highlight the point, I also tried to query some data for a specific date. However the dl_snapshot_date returned in the select is different than the value we specified in the where clause.
“statement”: "SELECT DL_SNAPSHOT_DATE, CAST(DL_SNAPSHOT_DATE as varchar) as casted_DT, UNIX_TIMESTAMP(DL_SNAPSHOT_DATE, ‘YYYY-MM-DD’) unixTimestampFunction_DT from someTable where DL_SNAPSHOT_DATE= ‘2022-08-30’
Note in the screenshot that the DL_SNAPSHOT_DATE returned is not the same we have in the filter criteria.
Is there something I’m doing wrong/missing or is this a bug with the driver?
Thanks in advance,
Jackson