0

I have two timestamps in epoch format in two columns in hive table T1. They actually represent the job start time and end time. I want to calculate the time duration for which the job ran. Hence I am casting the epoch times into timestamp datatype and subtracting them. This is giving me the output in terms of 'dd hh:mm:ss:ms'. Lets say the output is going into column 'duration' in table T2 which is of timestamp data type in the ddl. But when I am writing this data into the table T2, the job gives an error when half way through the mapreduce phase saying 'unknown data type: interval_day_time'. I am not able to use the 'interval_day_time' datatype in the ddl as even that is giving me an error.

Is there any other way around?

Djeah
  • 320
  • 8
  • 21
  • you are putting an incorrect datatype to timestamp. You should convert duration into seconds or minutes then save it into a integer datatype column. – jose_bacoy Mar 05 '18 at 19:37
  • 1
    Kindly post the query and sample data. – Gyanendra Dwivedi Mar 05 '18 at 19:55
  • I would leave it as epoch time and subtract them and that's how many seconds it took to run. Then just divide by 3600 if you want to know how many hours it took. One thing you may have to consider with subracting datetime stuff manually is when daylight savings hits, which you don't have to worry about with epoch time. – deusxmach1na Mar 05 '18 at 23:18
  • Possible duplicate of [How to calculate Date difference in Hive](https://stackoverflow.com/questions/30521669/how-to-calculate-date-difference-in-hive) – Vinayak Dornala Mar 06 '18 at 01:07

2 Answers2

0

Depends on how you want to store your "duration" field. The DATE_DIFF function can be used to calculate the difference between two timestamps dates in days. This value in days can then be converted to hours, minutes or seconds.

Rupert
  • 150
  • 1
  • 6
0

Well I couldn't get around it. So I created my own macro which calculates the time in hours, minutes and seconds using mathematical formula and outputs the value in string.

Djeah
  • 320
  • 8
  • 21