hivehiveqlgreenplum

Hive Migrate Date Format from Greenplum SQL

I am looking for help to convert below sql query into hive supported date format. kindly assist.

GP: SQL

select to_date('19800302000000','yyyymmddhh24miss') date_of_birth

GP Output : 1980-03-02

GP query :

extract(year from age(current_date-1, to_date(b.birthday,'yyyymmddhh24miss'))) age

we are looking similar out in hive. please help us.

Solution

  • For select to_date('19800302000000','yyyymmddhh24miss') use this
    select from_unixtime(unix_timestamp('19800302000000','yyyyMMddhhmmSS')).

    If you dont want time part, use this select to_date(from_unixtime(unix_timestamp('19800302000000','yyyyMMddhhmmSS'))).

    For extract(year from age(current_date-1, to_date(b.birthday,'yyyymmddhh24miss'))) age

    use below code. it should give difference of years between yesterday and DOB.

    select 
year(current_date() - interval 1 day ) - 
year(from_unixtime(unix_timestamp('19800302000000','yyyyMMddhhmmSS'))) age