By the way, I was looking for how to set user.timezone option. https://mapr.com/support/s/article/Understanding-Drill-s-timestamp-and-timezone?language=en_US It says sys.boot option user.timezone, set to UTC It does not exist anymore
https://mapr.com/support/s/article/MapR-DB-JSON-application-Error-Illegal-instant-due-to-time-zone-offset-transition?language=en_US I followed the instructions here. Add -Duser.timezone=UTC in DRILL_JAVA_OPTS sqlline.bat Regards, Herman On Wed, Oct 3, 2018 at 11:34 PM Herman Tan <[email protected]> wrote: > Hi, > > I ran SQL > SELECT TIMEOFDAY() FROM (VALUES(1)); > and I got: > 2018-10-03 15:27:16.586 Asia/Singapore > > I added -Duser.timezone=UTC in DRILL_JAVA_OPTS in the sqlline.bat > Restarted drill > > I ran SQL again > SELECT TIMEOFDAY() FROM (VALUES(1)); > and I got: > 2018-10-03 15:27:16.586 UTC > > I ran SQL that failed before: > select to_date('1982/01/01 00:00:00.000000000','yyyy/MM/dd > HH:mm:ss.SSSSSSSSS') -- fail > from (values(1)) > and it passes! > > Thank you for the help. > > Herman > > > On Wed, Oct 3, 2018 at 5:35 PM Vova Vysotskyi <[email protected]> wrote: > >> Hello Herman, >> >> I tried to reproduce this error, but all queries passed on my machine. >> Could you please add more details about your env? Which version of Drill >> is >> used, which timezone is set? >> Is it reproduced with UTC timezone? >> >> Kind regards, >> Volodymyr Vysotskyi >> >> >> On Mon, Oct 1, 2018 at 10:58 AM Herman Tan <[email protected]> wrote: >> >> > Hi, >> > >> > I have a very puzzling error. >> > Try the following SQL statements. >> > >> > What is the problem with '1982/01/01 00:01:00.000000000'? >> > Error message: Illegal instant due to time zone offset transition >> > >> > select to_date('1981/12/31 00:00:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- pass >> > from (values(1)) >> > >> > select to_date('1981/12/31 11:59:59.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- pass >> > from (values(1)) >> > >> > select to_date('1982/01/01 00:00:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- fail >> > from (values(1)) >> > >> > select to_date('1982/01/01 00:00:01.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- fail >> > from (values(1)) >> > >> > select to_date('1982/01/01 00:01:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- fail >> > from (values(1)) >> > >> > select to_date('1982/01/01 01:00:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- pass >> > from (values(1)) >> > >> > select to_date('1982/01/02 00:00:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- pass >> > from (values(1)) >> > >> > select to_date('1983/01/01 00:00:00.000000000','yyyy/MM/dd >> > HH:mm:ss.SSSSSSSSS') -- pass >> > from (values(1)) >> > >> > Herman >> > >> >
