Discussion:
to_date() string to date conversion ERROR
Herman Tan
2018-10-01 07:55:56 UTC
Permalink
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
Vova Vysotskyi
2018-10-03 09:35:34 UTC
Permalink
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
Post by Herman Tan
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
Herman Tan
2018-10-03 15:34:16 UTC
Permalink
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
Post by Vova Vysotskyi
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
Post by Herman Tan
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
Herman Tan
2018-10-03 15:44:13 UTC
Permalink
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
Post by Herman Tan
Hi,
I ran SQL
SELECT TIMEOFDAY() FROM (VALUES(1));
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));
2018-10-03 15:27:16.586 UTC
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
Post by Vova Vysotskyi
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
Post by Herman Tan
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
Khurram Faraaz
2018-10-03 18:00:19 UTC
Permalink
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
Post by Herman Tan
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
Post by Herman Tan
Hi,
I ran SQL
SELECT TIMEOFDAY() FROM (VALUES(1));
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));
2018-10-03 15:27:16.586 UTC
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
Post by Vova Vysotskyi
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
Post by Herman Tan
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
Loading...