1

My requirement is simple how to calculate the time difference between two column in hive

Example

Time_Start: 10:15:00

Time_End: 11:45:00

I need to do (Time_End-Time_Start) =1:30:00

Note both the columns are in String datatype kindly help to get required result..

Community
  • 1
  • 1
Elavarasan
  • 301
  • 1
  • 5
  • 14

2 Answers2

3

Language manual contains description of all available datetime functions. Difference in seconds can be calculated in such way: hour(time_end) * 3600 + minute(time_end) * 60 + second(time_end) - hour(time_start) * 3600 - minute(time_start) * 60 - second(time_start). You can wrap it with from_unixtime(..., 'HH:mm:ss') to get formatted time diff.

It might be better to write a UDF instead of putting all this into your query.

kozlice
  • 714
  • 7
  • 12
  • Thanks you so much !!! this conversion logic worked for my requirement and you saved my time :) +1 – Elavarasan Jan 13 '16 at 03:20
  • I could swear I got this to work a few days ago and now I am not sure what I am doing wrong? From_unixtime((hour(a) * 3600 + minute(a) * 60 + second(a), 'HH:mm:ss’) – From_unixtime (hour(b) * 3600 + minute(b) * 60 + second(b), 'HH:mm:ss’)) – bernando_vialli Apr 24 '19 at 23:37
  • @mkheifetz We can't really help without seeing an error message and the query you wrote (the fragment you have in your comment has quotes and dashes characters which definitely won't be accepted by the parser). – kozlice Apr 27 '19 at 13:42
  • so the weird thing is I am trying to just date the date out of the datetime and it works fine when I just pull that 1 column with the timestamp from the table but when I do it with other columns ina select statement unrelated to the timestamp I get: . . . . . . .> ; Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Invalid function 'Z.from_unixtime' (state=42000,code=40000) but I get get it to work when doing Select from_unixtime(unix_timestamp(ta,'dd/MM/yy HH:mm'),'yyyy-MM-dd') but not select a, from_unixtime(unix_timestamp(ta,'dd/MM/yy HH:mm'),'yyyy-MM-dd') – bernando_vialli Apr 28 '19 at 18:14
0

You need to convert the HH:MM:SS times to seconds, get the difference between them and re-arrange it as another HH:MM:SS time.

William Fernandes
  • 239
  • 2
  • 14
  • @williams I tried plenty of option , hive is not getting easy way to convert . For example I am able to convert from string to time but not able to find difference it showing null from below query SUBSTR(from_unixtime(UNIX_TIMESTAMP(Time_End , 'HH:mm:ss')),12)- SUBSTR(from_unixtime(UNIX_TIMESTAMP(Time_Start , 'HH:mm:ss')),12) I hope you should see in hive percpective – Elavarasan Jan 13 '16 at 02:48
  • Yes , but again am asking from hive perspective . If I write java to get this time difference alone my code will go too much complex .... – Elavarasan Jan 13 '16 at 02:56