Date and timestamp format changes

Learn about the change in the way date and timestamp values are parsed.

Before Upgrade to CDP 7.1.7 SP2

Some of the Hive date and timestamp functions used the SimpleDateFormat class for formatting and parsing date and timestamp. For more information, refer to the SimpleDateFormat class Javadocs.

After Upgrade to CDP 7.1.7 SP2

The following Hive date and timestamp functions are now enhanced to use the DateTimeFormatter class for printing and parsing date and timestamp objects. For more information, refer to the DateTimeFormatter class Javadocs.

unix_timestamp(): This function is enhanced to use the DateTimeFormatter class for String format dates instead of the SimpleDateFormat class. For details, see HIVE-25458.
from_unixtime(): This function is now enhanced to consider leap seconds. For details, see HIVE-25403.
date_format(): This function that previously returned the output in UTC time zone is enhanced to display the default user session time zone. For details, see HIVE-25093.
cast(): This function is enhanced to display NULL when an incorrect date or timestamp is casted. Prior to this enhancement, when an incorrect date was casted, the function returned a converted value. For example, cast ('2020-20-20' as date) resulted in '2021-08-20' instead of NULL.
This is because the DateTimeFormatter class that is used to parse string into date or timestamp was set to ResolverStyle.LENIENT. This is now updated to use ResolverStyle.STRICT and returns NULL when an invalid date or timestamp is casted. For details, see HIVE-25306.

important

Two identical patterns can be interpreted differently by the SimpleDateFormat and DateTimeFormatter class leading to different results. Even if the same letters appear in the javadoc of both the classes, their semantics might be different. Therefore, it is important that you read the javadoc carefully to understand the behavior.

For example, consider the pattern "DD". In both SimpleDateFormat and DateTimeFormatter classes, the letter "D" represents the day in a year. However, the number of occurrences is interpreted differently by the classes.

If the date - July 19, 2023 is formatted using SimpleDateFormat("D") , the output results in 231.

If the same date - July 19, 2023 is formatted using DateTimeFormatter("D"), the output results in the following error:

Exception in thread "main" java.time.DateTimeException: Field DayOfYear cannot be printed as the value 200 exceeds the maximum print width of 2

For more details about the syntax and behavior of these UDFs, see Hive LanguageManual UDF.