drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Khurram Faraaz <kfar...@mapr.com>
Subject Re: to_date() string to date conversion ERROR
Date Wed, 03 Oct 2018 18:00:19 GMT
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 <herman@redcubesg.com> 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 <herman@redcubesg.com> 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 <vvovyk@gmail.com> 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 <herman@redcubesg.com>
> 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
> >> >
> >>
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message