Search code examples
hadoophiveunix-timestamp

Hive - time difference in minutes is negative

I need to get time difference in minutes for my analysis in Hive query.

I am using unix_timestamp() to convert dates to seconds and then subtracting to get the diff in seconds and the multiplied by 60 for minutes.

My issue is my recent date - older date difference is coming negative.

here is my query and results

Hive query and result screenshot

processed_ts create_ts processed_unix_timestamp create_unix_timestamp miniue Diff 2017-03-12 3:01:06 2017-03-12 2:58:36 1489312865 1489316315 -57.5 2017-03-12 3:01:36 2017-03-12 2:59:06 1489312895 1489316345 -57.5 2017-03-12 3:02:12 2017-03-12 2:59:42 1489312932 1489316382 -57.5

Any help is much appreciated.

Solution

  • USA & Canada Start DST on March 12
    Published 17-Feb-2017

    Most of the United States, Canada, and Mexico's northern border cities will begin Daylight Saving Time (DST) on Sunday, March 12, 2017.

    People in areas that observe DST will spring forward 1 hour from 02:00 (2 am) to 03:00 (3 am), local time. Standard time will resume on Sunday, November 5, 2017.

    https://www.timeanddate.com/news/time/usa-canada-start-dst-2017.html

    select timestamp '2017-03-12 02:58:36'

    2017-03-12 03:58:36