*History*
*Hive 1.2* -
VM time zone set to Asia/Bangkok
*Query* - SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('1800-01-01 00:00:00
UTC','yyyy-MM-dd HH:mm:ss z'));
*Result* - 1800-01-01 07:00:00
*Implementation details* -
SimpleDateFormat formatter = new SimpleDateFormat(pattern);
Long unixtime = formatter.parse(textval).getTime() / 1000;
Date date = new Date(unixtime * 1000L);
https://docs.oracle.com/javase/8/docs/api/java/util/Date.html . In official
documentation they have mentioned that "Unfortunately, the API for these
functions was not amenable to internationalization and The corresponding
methods in Date are deprecated" . Due to that this is producing wrong result
*latest hive* -
set hive.local.time.zone=Asia/Bangkok;
*Query* - SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('1800-01-01 00:00:00
UTC','yyyy-MM-dd HH:mm:ss z'));
*Result* - 1800-01-01 06:42:04
*Implementation details* -
DateTimeFormatter dtformatter = new DateTimeFormatterBuilder()
.parseCaseInsensitive()
.appendPattern(pattern)
.toFormatter();
ZonedDateTime zonedDateTime =
ZonedDateTime.parse(textval,dtformatter).withZoneSameInstant(ZoneId.of(timezone));
Long dttime = zonedDateTime.toInstant().getEpochSecond();
*Problem*-
Now *SimpleDateFormat* has been replaced with *DateTimeFormatter* which is
not backward compatible. Causing issues at times for migration to the new
version. Because the older data written using Hive 1.x or 2.x is not
compatible with *DateTimeFormatter*.
*Solution -*
Introduce an config "hive.legacy.timeParserPolicy" with following values -
*1. EXCEPTION* - compare value of
both SimpleDateFormat & DateTimeFormatter raise exception if doesn't match
*2. LEGACY *- use SimpleDateFormat
*3. CORRECTED *- use DateTimeFormatter
This will help hive user in the following manner -
1. Migrate to new version using *LEGACY*
2. Find values which are not compatible with the new version - *EXCEPTION*
3. Use latest date apis - *CORRECTED*
Note: apache spark also face the same issue
https://issues.apache.org/jira/browse/SPARK-30668
Hive jira - https://issues.apache.org/jira/browse/HIVE-25576
Thanks
Ashish Sharma
