HIVE - Format of time difference

I want to calculate the difference between to events. Both events are stored in the UNIX_TIMESTAMP in miliseconds.

Here is an example: 1464740049000

How can I convert the time difference and format them in hours.

I tried several things like datediff and/or timestamp(event1) - timestamp(event2).

What I want is

select timestamp(e1.time), timestamp(e2.time), e1.time-e2.time as Time_Diff from testdata;

Time_Diff should be formatted in hours, minutes, seconds... How can I get this? Thanks in advance Peter

Answers


If you are interested in Hour only, Instead of converting the unixtimestamp to timestamp, just use SQL Mathematical functions

select (time2 - time1)/(1000 * 60 * 60) as hours from mytable; 

If the difference is less than 24 hours, you can use this:

[localhost:21000] > select from_unixtime(1392394861 - 1392394860, 'HH:mm:ss');
+----------------------------------------------------+
| from_unixtime(1392394861 - 1392394860, 'hh:mm:ss') |
+----------------------------------------------------+
| 00:00:01                                           |
+----------------------------------------------------+

If the difference can be more than 24 hours, the following rather ugly expression will do the trick:

[localhost:21000] > select concat(cast(floor((1392394861 - 1392300000)/60/60) as string), from_unixtime(1392394861 - 1392300000, ':mm:ss'));
+----------------------------------------------------------------------------------------------------------------------+
| concat(cast(floor((1392394861 - 1392300000) / 60 / 60) as string), from_unixtime(1392394861 - 1392300000, ':mm:ss')) |
+----------------------------------------------------------------------------------------------------------------------+
| 26:21:01                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------+

Or if you prefer a format with explicit days:

[localhost:21000] > select concat(cast(floor((1392394861 - 1392300000)/60/60/24) as string), " days and ", from_unixtime(1392394861 - 1392300000, 'HH:mm:ss'));
+-------------------------------------------------------------------------------------------------------------------------------------------+
| concat(cast(floor((1392394861 - 1392300000) / 60 / 60 / 24) as string), ' days and ', from_unixtime(1392394861 - 1392300000, 'hh:mm:ss')) |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 1 days and 02:21:01                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Need Your Help

Spring transaction isolation not working

java spring schedule transactional isolation

I'm making a very simple example to test Spring isolation. I have 2 very similar Schedule classes:

SCM for shared hosting

php svn version-control shared-hosting

I have a sharing hosting, and obviously I don't have any SCM (Source Control Management) on it, but having to deploy every small changes here and there every now and then is frustrating.