Timestamp or date related behaviors A few behavior changes are related to timestamp and date formats, functions, and data types. ADD_MONTHS function fixThe output of the ADD_MONTHS function on a timestamp column was corrected to include the time.ADD_MONTHS date validationA change, which is not likely to cause any migration problems, was made to the ADD_MONTHS function.Casting invalid datesCasting of an invalid date differs from Hive 1 in CDH 5 to Hive 3 in CDP. Hive 3 uses a different parser formatter from the one used in Hive 1, which affects semantics. Hive 1 considers 00 invalid for date fields. Hive 3 considers 00 valid for date fields. Neither Hive 1 nor Hive 3 correctly handles invalid dates, and Hive-25056 addresses this issue.FROM_UNIXTIME and UNIX_TIMESTAMP time zoneThe FROM_UNIXTIME and UNIX_TIMESTAMP functions have undergone more than one time zone change. Handling of CURRENT_TIMESTAMP output formatThe output format of the CURRENT_TIMESTAMP user defined function (UDF) is inconsistent in certain scenarios.Handling of Julian dates in UDFsThe way Julian dates are handled in CDP is improved over the way CDH handled these dates.Handling return type for old date functionsLearn about the changes related to the return type for some of the originally introduced date functions. The return type is changed from string to date.Support for SQL:2016 datetime formats (limited formats)This change introduces support for the SQL:2016 FORMAT clause for CAST which is the most widely used method to perform string to datetime conversions. The change also includes support for a limited list of SQL:2016 datetime formats.UNIX_TIMESTAMP behaviorThe behavior of the UNIX_TIMESTAMP function in CDP differ in several ways from CDH.TIMESTAMP based on UTCThe Hive TIMESTAMP in CDH was not UTC-based. CDP supports UTC-based, SQL TIMESTAMP.UNIX_TIMESTAMP conversion of TIMESTAMPLOCALTZCDP corrects an issue in CDH related to the UNIX_TIMESTAMP conversion of a local time zone.Parent topic: Syntax and semantic changes CDH 6.2.1 to CDP 7.0.3.2
