Re: to_date() string to date conversion ERROR
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 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 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.0','/MM/dd
> > HH:mm:ss.S') -- fail
> > from (values(1))
> > and it passes!
> >
> > Thank you for the help.
> >
> > Herman
> >
> >
> > On Wed, Oct 3, 2018 at 5:35 PM Vova Vysotskyi 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
> wrote:
> >>
> >> > Hi,
> >> >
> >> > I have a very puzzling error.
> >> > Try the following SQL statements.
> >> >
> >> > What is the problem with '1982/01/01 00:01:00.0'?
> >> > Error message: Illegal instant due to time zone offset transition
> >> >
> >> > select to_date('1981/12/31 00:00:00.0','/MM/dd
> >> > HH:mm:ss.S') -- pass
> >> > from (values(1))
> >> >
> >> > select to_date('1981/12/31 11:59:59.0','/MM/dd
> >> > HH:mm:ss.S') -- pass
> >> > from (values(1))
> >> >
> >> > select to_date('1982/01/01 00:00:00.0','/MM/dd
> >> > HH:mm:ss.S') -- fail
> >> > from (values(1))
> >> >
> >> > select to_date('1982/01/01 00:00:01.0','/MM/dd
> >> > HH:mm:ss.S') -- fail
> >> > from (values(1))
> >> >
> >> > select to_date('1982/01/01 00:01:00.0','/MM/dd
> >> > HH:mm:ss.S') -- fail
> >> > from (values(1))
> >> >
> >> > select to_date('1982/01/01 01:00:00.0','/MM/dd
> >> > HH:mm:ss.S') -- pass
> >> > from (values(1))
> >> >
> >> > select to_date('1982/01/02 00:00:00.0','/MM/dd
> >> > HH:mm:ss.S') -- pass
> >> > from (values(1))
> >> >
> >> > select to_date('1983/01/01 00:00:00.0','/MM/dd
> >> > HH:mm:ss.S') -- pass
> >> > from (values(1))
> >> >
> >> > Herman
> >> >
> >>
> >
>
Re: to_date() string to date conversion ERROR
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 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.0','/MM/dd
> HH:mm:ss.S') -- fail
> from (values(1))
> and it passes!
>
> Thank you for the help.
>
> Herman
>
>
> On Wed, Oct 3, 2018 at 5:35 PM Vova Vysotskyi 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 wrote:
>>
>> > Hi,
>> >
>> > I have a very puzzling error.
>> > Try the following SQL statements.
>> >
>> > What is the problem with '1982/01/01 00:01:00.0'?
>> > Error message: Illegal instant due to time zone offset transition
>> >
>> > select to_date('1981/12/31 00:00:00.0','/MM/dd
>> > HH:mm:ss.S') -- pass
>> > from (values(1))
>> >
>> > select to_date('1981/12/31 11:59:59.0','/MM/dd
>> > HH:mm:ss.S') -- pass
>> > from (values(1))
>> >
>> > select to_date('1982/01/01 00:00:00.0','/MM/dd
>> > HH:mm:ss.S') -- fail
>> > from (values(1))
>> >
>> > select to_date('1982/01/01 00:00:01.0','/MM/dd
>> > HH:mm:ss.S') -- fail
>> > from (values(1))
>> >
>> > select to_date('1982/01/01 00:01:00.0','/MM/dd
>> > HH:mm:ss.S') -- fail
>> > from (values(1))
>> >
>> > select to_date('1982/01/01 01:00:00.0','/MM/dd
>> > HH:mm:ss.S') -- pass
>> > from (values(1))
>> >
>> > select to_date('1982/01/02 00:00:00.0','/MM/dd
>> > HH:mm:ss.S') -- pass
>> > from (values(1))
>> >
>> > select to_date('1983/01/01 00:00:00.0','/MM/dd
>> > HH:mm:ss.S') -- pass
>> > from (values(1))
>> >
>> > Herman
>> >
>>
>
Re: to_date() string to date conversion ERROR
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.0','/MM/dd
HH:mm:ss.S') -- fail
from (values(1))
and it passes!
Thank you for the help.
Herman
On Wed, Oct 3, 2018 at 5:35 PM Vova Vysotskyi 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 wrote:
>
> > Hi,
> >
> > I have a very puzzling error.
> > Try the following SQL statements.
> >
> > What is the problem with '1982/01/01 00:01:00.0'?
> > Error message: Illegal instant due to time zone offset transition
> >
> > select to_date('1981/12/31 00:00:00.0','/MM/dd
> > HH:mm:ss.S') -- pass
> > from (values(1))
> >
> > select to_date('1981/12/31 11:59:59.0','/MM/dd
> > HH:mm:ss.S') -- pass
> > from (values(1))
> >
> > select to_date('1982/01/01 00:00:00.0','/MM/dd
> > HH:mm:ss.S') -- fail
> > from (values(1))
> >
> > select to_date('1982/01/01 00:00:01.0','/MM/dd
> > HH:mm:ss.S') -- fail
> > from (values(1))
> >
> > select to_date('1982/01/01 00:01:00.0','/MM/dd
> > HH:mm:ss.S') -- fail
> > from (values(1))
> >
> > select to_date('1982/01/01 01:00:00.0','/MM/dd
> > HH:mm:ss.S') -- pass
> > from (values(1))
> >
> > select to_date('1982/01/02 00:00:00.0','/MM/dd
> > HH:mm:ss.S') -- pass
> > from (values(1))
> >
> > select to_date('1983/01/01 00:00:00.0','/MM/dd
> > HH:mm:ss.S') -- pass
> > from (values(1))
> >
> > Herman
> >
>
Re: to_date() string to date conversion ERROR
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 wrote:
> Hi,
>
> I have a very puzzling error.
> Try the following SQL statements.
>
> What is the problem with '1982/01/01 00:01:00.0'?
> Error message: Illegal instant due to time zone offset transition
>
> select to_date('1981/12/31 00:00:00.0','/MM/dd
> HH:mm:ss.S') -- pass
> from (values(1))
>
> select to_date('1981/12/31 11:59:59.0','/MM/dd
> HH:mm:ss.S') -- pass
> from (values(1))
>
> select to_date('1982/01/01 00:00:00.0','/MM/dd
> HH:mm:ss.S') -- fail
> from (values(1))
>
> select to_date('1982/01/01 00:00:01.0','/MM/dd
> HH:mm:ss.S') -- fail
> from (values(1))
>
> select to_date('1982/01/01 00:01:00.0','/MM/dd
> HH:mm:ss.S') -- fail
> from (values(1))
>
> select to_date('1982/01/01 01:00:00.0','/MM/dd
> HH:mm:ss.S') -- pass
> from (values(1))
>
> select to_date('1982/01/02 00:00:00.0','/MM/dd
> HH:mm:ss.S') -- pass
> from (values(1))
>
> select to_date('1983/01/01 00:00:00.0','/MM/dd
> HH:mm:ss.S') -- pass
> from (values(1))
>
> Herman
>
to_date() string to date conversion ERROR
Hi,
I have a very puzzling error.
Try the following SQL statements.
What is the problem with '1982/01/01 00:01:00.0'?
Error message: Illegal instant due to time zone offset transition
select to_date('1981/12/31 00:00:00.0','/MM/dd
HH:mm:ss.S') -- pass
from (values(1))
select to_date('1981/12/31 11:59:59.0','/MM/dd
HH:mm:ss.S') -- pass
from (values(1))
select to_date('1982/01/01 00:00:00.0','/MM/dd
HH:mm:ss.S') -- fail
from (values(1))
select to_date('1982/01/01 00:00:01.0','/MM/dd
HH:mm:ss.S') -- fail
from (values(1))
select to_date('1982/01/01 00:01:00.0','/MM/dd
HH:mm:ss.S') -- fail
from (values(1))
select to_date('1982/01/01 01:00:00.0','/MM/dd
HH:mm:ss.S') -- pass
from (values(1))
select to_date('1982/01/02 00:00:00.0','/MM/dd
HH:mm:ss.S') -- pass
from (values(1))
select to_date('1983/01/01 00:00:00.0','/MM/dd
HH:mm:ss.S') -- pass
from (values(1))
Herman
