Hello Herman, That information about setting the timezone is available here in the Apache Drill docs https://drill.apache.org/docs/data-type-conversion/
Configure the default time zone format in /conf/drill-env.sh by adding -Duser.timezone=UTC to DRILL_JAVA_OPTS. For example: export DRILL_JAVA_OPTS="-Xms1G -Xmx$DRILL_MAX_HEAP -XX:MaxDirectMemorySize=$DRILL_MAX_DIRECT_MEMORY -XX:MaxPermSize=512M -XX:ReservedCodeCacheSize=1G -ea -Duser.timezone=UTC" Regards, Khurram On Wed, Oct 3, 2018 at 8:44 AM Herman Tan <[email protected]> wrote: > 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 > >> > > >> > > >
