How to find time difference between two epoch times in hive

Discussion in 'Big Data and Analytics' started by Ranjan K. Mishra, Jan 5, 2017.

  1. Ranjan K. Mishra

    Joined:
    Oct 11, 2016
    Messages:
    11
    Likes Received:
    0
    I have two columns in a hive table loaded with unix epoch times
    start time, end time
    1235000081 1235000501

    I need to find the time difference in mins or hours or seconds in hive, tried several functions like converting it to unix_timestamp , to_date, datediff etc. but I am getting nulls.
     
    #1
  2. Karthik Shivana

    Karthik Shivana Moderator
    Simplilearn Support Alumni

    Joined:
    Apr 1, 2016
    Messages:
    674
    Likes Received:
    28

    Hi Ranjan,

    Apologize for the inconvenience, please confirm the data-set is loaded into hive table properly?
     
    #2
    Last edited: Jan 6, 2017
  3. Ranjan K. Mishra

    Joined:
    Oct 11, 2016
    Messages:
    11
    Likes Received:
    0

    Hi Karthik,
    I didn't understand your response. Yes, like I said data is loaded into hive table

    I have also given the sample data for one row
     
    #3
  4. Ranjan K. Mishra

    Joined:
    Oct 11, 2016
    Messages:
    11
    Likes Received:
    0
    yes, dataset is loaded I can see data in the hive table in hue.

    sno string from deserializer
    unq_ques_id string from deserializer
    user_id_questioner string from deserializer
    ques_score string from deserializer
    time_ques_asked string from deserializer
    html_tags1 string from deserializer
    no_times_viewed string from deserializer
    no_of_ans_submitted string from deserializer
    unq_ans_id string from deserializer
    user_id_response string from deserializer
    ans_score string from deserializer
    time_response string from deserializer


    1 563355 62701 0 1235000081 php,error,gd,image-processing 220 2 563372 67183 2 1235000501
    2 563355 62701 0 1235000081 php,error,gd,image-processing 220 2 563374 66554 0 1235000551
    3 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563358 15842 3 1235000177
    4 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563413 893 18 1235001545
    5 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563454 11649 4 1235002457
    6 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563472 50742 6 1235002809
    7 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563484 8899 1 1235003266
    8 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563635 60190 12 1235007817
    9 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 563642 65235 1 1235007913
    10 563356 15842 10 1235000140 lisp,scheme,subjective,clojure 1047 16 564028 32797 8 1235020626
    Time taken: 0.389 seconds, Fetched: 10 row(s)
     
    #4
  5. Ranjan K. Mishra

    Joined:
    Oct 11, 2016
    Messages:
    11
    Likes Received:
    0
    found the solution, it was so simple. Just subtract the difference divide by 60 to get in minutes. The epoch time is in seconds, for milliseconds just multiple by 1000
     
    #5
  6. DeshDeep Singh

    DeshDeep Singh Well-Known Member
    Simplilearn Support Alumni

    Joined:
    Sep 22, 2015
    Messages:
    934
    Likes Received:
    66

    HI Ranjan,

    You could use unix_timestamp for dates after 1970:

    SELECT(unix_timestamp('2013-01-01 10:10:10')- unix_timestamp('1970-01-01 00:00:00'))/60
    1. Convert both dates to seconds from 1970-01-01
    2. Substract them
    3. Divide by 60 to get minutes


    EDIT:

    Adding Minutes: change date to unixtime -> add var * 60sec -> convert back to date

    SELECT from_unixtime(unix_timestamp('2013-01-01 10:10:10')+10*60)AS result
     
    #6

Share This Page