[HIVE-25499] select unix_timestamp(dt) from table and select unix_timestamp(constant date) are different

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.2
• Assignee: Ashish Sharma
• Reporter: zhaolong
• Created: 04/Sep/21 06:58
• Updated: 06/Sep/21 08:50
• Resolved: 04/Sep/21 10:40

Description

I found select unix_timestamp(date column) from table and select unix_timestamp(constant date) are different in 3.1.2, for example:

create table testdate(dt date);
insert into testdate values('0001-12-30');

select * from testdate; --> 0001-12-30

select unix_timestamp(dt) from testdate; --> -62104233600

select unix_timestamp('0001-12-30', 'yyyy-MM-dd'); --> -62104406400

the -62104233600 is different with -62104406400.

 

and convert timestap value is:

select from_unixtime(-62104233600); --> 0002-01-01 00:00:00 , 62104233600 is  select unix_timestamp(date column) from table value which date is 0001-12-30.

select from_unixtime(-62104406400); --> 0001-12-30 00:00:00

 

Issue Links

• duplicates: HIVE-25458 unix_timestamp() with string input give wrong result