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

["zhaolong (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-25499?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17409902#comment-17409902 ] 

zhaolong commented on HIVE-25499:
---------------------------------

It looks like,

data column convert to timestamp use:

LocalDate localDate = LocalDate.of(0001, 12, 30);
long localTime = localDate.atStartOfDay().toEpochSecond(ZoneOffset.of("+0"));
System.out.println(localTime);

 

and constant data convert to timestamp use:

String date = "0001-12-30";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date1 = sdf.parse(date);
System.out.println(date1.getTime()/1000);

> select unix_timestamp(dt) from table and select unix_timestamp(constant date)  are different
> --------------------------------------------------------------------------------------------
>
>                 Key: HIVE-25499
>                 URL: https://issues.apache.org/jira/browse/HIVE-25499
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 3.1.2
>            Reporter: zhaolong
>            Priority: Major
>
> 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
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)