Timezone issue when retrieving date from Elasticsearch (UTC considered as local)

[milantar]

Hello,
It seems that a datefield retrieved by opendistro jdbc (opendistro-sql-jdbc-1.2.0.0.jar) from Elasticsearch (7.2.0) is retrieved as local (displayed in JasperServer).
Elasticsearch stores the date as UTC:
{ "_index" : "record-deletion-logs", "_type" : "_doc", "_id" : "jhTO1HcBu5s5PDfU9iUG", "_score" : 8.962324, "_source" : { ... "operationdate" : "2021-01-18T11:17:41.529Z", }
Afetr querying with opendistro:
select external_id, operationdate from record-deletion-logs WHERE external_id like 100801210112003515%'
the date '2021-01-18 11:17:41.529' is considered local (TZ +1 in my case), so I display the UTC date at the local time. If I override the timezone, the date shifts accordingly, if I force the timezone to UTC the resulting date is '2021-01-18 10:17:41.529'.
Attached a screenshot of the date retrieved from Jasper

[jcgraybill]

Hi @milantar, moving this to the jdbc-driver repo...

[chloe-zh]

Hi @milantar thanks for reporting this issue.
I was able to reproduce this issue locally. To my understanding, the query engine sets the system timezone to UTC by default as of now rather than exposes the timezone info directly to the plugin. In general, it should be eligible for users to set custom timezone as system timezone, which is not supported by our engine yet. Would we have any workaround on this? @penghuo @dai-chen

[milantar]

Thanks Chloe, I'm still waiting for a workaround. Anything to propose? Kind Regards Le sam. 27 févr. 2021 à 08:37, Chloe <notifications@github.com> a écrit :

…

Hi @milantar <https://github.com/milantar> thanks for reporting this issue. I was able to reproduce this issue locally. To my understanding, the query engine sets the system timezone to UTC by default as of now rather than exposes the timezone info directly to the plugin. In general, it should be eligible for users to set custom timezone as system timezone, which is not supported by our engine yet. Would we have any workaround on this? @penghuo <https://github.com/penghuo> @dai-chen <https://github.com/dai-chen> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1062 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKLNFTBCTA3N3VKTOXOV4FTTBCOMBANCNFSM4YG4WDOA> .