UNIX_TIMESTAMP behavior

The behavior of the UNIX_TIMESTAMP function in CDP differ in several ways from CDH.

Before Upgrade to CDP

• In CDH, you can use lowercase hh to represent hours of a timestamp in the format specification.
• You do not have to include microseconds in the format specification. The format MM-dd-yyyy HH:mm:ss is sufficient.
• Casting a unix timestamp as a string works.

Example of using hh, which works:

select unix_timestamp('2024-12-30 59:10:20', "yyyy-MM-dd hh:mm:ss"), from_utc_timestamp(from_unixtime(unix_timestamp('2024-12-30 59:10:20', "yyyy-MM-dd hh:mm:ss"),'yyyy-MM-dd HH:mm:ss'), 'CST'), from_utc_timestamp(from_unixtime(unix_timestamp('2024-12-30 T59:10:20.192+0000', "yyyy-MM-dd'T'hh:mm:ss.SSS'+0000'"),'yyyy-MM-dd HH:mm:ss'), 'CST')  ;

Example of missing microseconds, which works.

select unix_timestamp('12-12-2023 15:30:12.075','MM-dd-yyyy HH:mm:ss');

Example of casting timestamp as a string, which works:

select cast(unix_timestamp('2023-04-03:10:10:00', 'yyyyMM') as string);

After Upgrade to CDP

• Hive in CDP requires uppercase HH and microseconds SSS are required in the format specification. For example, 'MM-dd-yyyy HH:mm:ss.SSS'; otherwise, output is NULL.
• Casting a timestamp yields NULL.

Example of using hh

Action Required

Change code from hh to HH and define microseconds.

select unix_timestamp('11-11-2020 15:30:12.084','MM-dd-yyyy HH:mm:ss.SSS');  

For more information about timestamp issues, see HIVE-25458 and CDP release notes.