Any better way to convert timestamp (HH:mm:ss) to Seconds in Hive

user_14451958 0
Viewed 614

I have one hive field of type string which has timestamp in following format: HH:mm:ss mm:ss ss I need to convert them as below:

Input: 
10:30:40
   30:40
      40
Output Expected:
    10:30:40 = (10*3600) + (30 * 60) + 40  = 37,840
       30:40 =             (30 * 60) + 40  =   1840
          40 =                         40  =     40

I tried doing something like this

case 
    when duration  like '%:%:%' then 
            split(duration, ':')[0] * 3600 + 
            split(duration, ':')[1] * 60 + 
            split(duration, ':')[2] 
        when duration  like  '%:%' then 
            split(duration, ':')[0] * 60 + 
            split(duration, ':')[1] 
        else 
            duration 
        end

This works but seems inefficient way. is there a better way to do the same when I have to process billions of records.

hiveql hive timestamp time sql
1 Answers

Your expressions will not create much additional load when executed in hive. You can simplify query a bit using unix_timestamp function, but it will run not faster.

with input as(--use your table instead of this
select stack(3, '10:30:40',
                '30:40',
                '40') as duration
)

select duration, case when duration like '%:%:%' then unix_timestamp(duration,'HH:mm:ss') 
                      when duration like '%:%'   then unix_timestamp(duration,'mm:ss') 
                      else duration
                  end as result
 from input

Result:

duration    result
10:30:40    37840
30:40       1840
40          40

Or even simpler:

select duration, coalesce(unix_timestamp(duration,'HH:mm:ss'), unix_timestamp(duration,'mm:ss'), duration) as result

returns exactly the same.

user_27003440
Related