Hive Timestamp value change after joining two tables

Question

I have two Hive tables, one holds a date value with Timestamp datatype. If I query on one specific record using the key it shows the date value correctly. select acct_key, account_open_date from Table_1 where acct_key=1234;

 acct_id   account_open_date
 1234      1963-03-01 00:00:00

However when joining this table with another table, the returned timestamp value changed to some value in year 2031 select a.acct_key, b.account_open_date from Table_2 a left outer join on Table_1 b on a.acct_key=b.acct_key;

acct_id    account_open_date
 1234       2031-03-19 00:00:00

Seems this issues only occurs for date value before Unix epoch time(1970). Any suggestion? Thanks

Answer 1

Two issues here, first thing join not working with timestamp and epoch timestamp. With your last line, I assume that the join is returning correct timestamp for other timestamp. Correct me if I am wrong. So if that is resolved, you can look into here to handle epoch time

Answer 2

I was unable to reproduce what you are seeing but nonetheless, you could try casting account_open_date to string.

select a.acct_id
    , b.new
    , other_columns
from db.table1
left outer join (
  select *
    , cast(account_open_date as string) new
  from db.table2 ) b
on a.acct_id=b.acct_id

Answer 3

I tried. Cast timestamp as String in a nested query worked as below. I also tried without nested query, but that does not work. Anyone knows why?

Not worked version:

SELECT 
   a.acct_id
  ,CAST(b.account_open_date AS STRING) new
  ,other_columns 
FROM 
  db.table1 a, 
  LEFT OUTER JOIN db.table2 b ON (a.acct_id = b.acct_id)
;

worked version:

SELECT 
     a.acct_id
    ,b.new
    ,other_columns
FROM 
    db.table1 a
    LEFT OUTER JOIN (
        SELECT 
           *
          ,CAST(account_open_date AS STRING) new
       FROM 
          db.table2
    ) b
       ON (a.acct_id=b.acct_id)