Search code examples
hivehiveqlunix-timestampepochtop-n

Hive joining columns with milliseconds

I have a table having columns id,create_time,code. create_time column is of type string having timestamp value in the format yyyy-MM-dd HH:mm:ss.SSSSSS Now my requirement is to find the latest code(recent create_time) for each id. If the create_time column has no milliseconds part, I can do 

select id,create_time,code from(
select id,max(unix_timestamp(create_time,"yyyy-MM-dd HH:mm:ss")) over (partition by id) as latest_time from table)a
join table b on a.latest_time=b.create_time

As unix time functions consider only seconds not milliseconds, am not able to proceed with them.

Please help

Solution

  • Why would you try to convert at all? Since you are only looking for the latest timestamp I would just do:

    select id,create_time,code from(
select id,max(create_time) over (partition by id) as latest_time from table)a
join table b on a.latest_time=b.create_time

    The ones without miliseconds will be treated, as they would have "000000" instead.