drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Herman Tan <her...@redcubesg.com>
Subject Re: to_date() string to date conversion ERROR
Date Wed, 03 Oct 2018 15:44:13 GMT
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