Is there a data type for time format hh:mm:ss in Hive
Storing duration as an Integer number of seconds seems like the best option for efficiency and for being able to do calcuations. I don't think you need a custom UDF to convert from your String to an Int. It can be done by combining existing UDFS:
Select 3600 * hours + 60 * minutes + seconds as duration_secondsFROM ( Select cast(substr(duration,1,2) as Int) as hours, cast(substr(duration,4,2) as Int) as minutes, cast(substr(duration,7,2) as Int) as seconds From( Select "01:02:03" as duration) a) b;
Hive provides built-in date functions to extract hour, minutes and seconds.https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
But if these functions doesn't help you directly and you use many combination of builtin function then i would suggest you to write your own UDF (in case this is very frequent utility and you run over large number of rows). You will see query performance difference.
Hope this helps