Calculate Difference Between Start_Time and End_Time in Seconds from Unix_Time Yyyy-Mm-Dd Hh:Mm:Ss

Calculate difference between start_time and end_time in seconds from unix_time yyyy-MM-dd HH:mm:ss

Solution for Hive.

Difference in seconds:

select UNIX_TIMESTAMP('2018-06-19T14:01:17.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS")-
   UNIX_TIMESTAMP('2018-06-19T13:59:41.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS") as seconds_diff

Result:

96

Now calculate difference in HH:mm:ss:

select concat_ws(':',lpad(floor(seconds_diff/3600),2,'0'),        --HH
                     lpad(floor(seconds_diff%3600/60),2,'0'),     --mm
                     lpad(floor(seconds_diff%3600%60),2,'0')      --ss
       )

from
(
select --calculate seconds difference
       UNIX_TIMESTAMP('2018-06-19T14:01:17.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS")-
       UNIX_TIMESTAMP('2018-06-19T13:59:41.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS") as seconds_diff
) s

Result:

OK
00:01:36
Time taken: 1.071 seconds, Fetched: 1 row(s)

See also this answer about format convertion: https://stackoverflow.com/a/23520257/2700344

HIVE:how to calculate seconds difference of time format: yyyyMMdd HH:mm:ss

Use UNIX_TIMESTAMP function to convert timestamps to seconds, then subtract:

select UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss');

Returns:

7993 seconds.

Difference in 'HH:mm:ss' format:

select from_unixtime(UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss'), 'HH:mm:ss');

Returns:

02:13:13

Also you can use solution how to format seconds in 'HH:mm:ss' using explicit math proposed in this answer: https://stackoverflow.com/a/57497316/2700344

Hive - calculating string type timestamp differences in minutes

select anonymousid,
       (max(unix_timestamp(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) - 
        min(unix_timestamp(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) 
       ) / 60
from db1.formevent
group by anonymousid;

Need to subtract some hours from given timestamp in hive

It works fine.

select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss') 

Difference between two dates in MySQL

SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format

SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- result: 79259  the difference in seconds

So, you can use TIMESTAMPDIFF for your purpose.

Hive/SQL Error when converting milliseconds to DDDD:HH:mm:ss

The logic is simple math. BIGINT timestamp is the number of seconds or milliseconds passed from Unix Epoch (1970-01-01 00:00:00 UTC).

To get milliseconds part use (ts % 1000) - returns reminder after division by 1000

To get total whole seconds passed, use (ts div 1000) - returns integer part, all other figures will be calculated from this number: days, hours, minutes, seconds.

days: (ts div 1000) div 86400 - returns integer part after division of total seconds by number of seconds in a day

To get hours left after whole days calculation: take reminder after days calculation ((ts div 1000) % 86400) and divide by number of seconds in hour, take integer part (((ts div 1000) % 86400) div 3600)

And so on.

Demo:

with your_data as (
select 1 id, bigint(2513702864) ts union all
select 2, bigint(17259) union all
select 3,bigint(127259) union all
select 4,bigint(1272) union all
select 5,bigint(127)
)

select --format output as required. For example days:hours:minutes:seconds.millis
       concat(days,':',hours,':',minutes,':',seconds,'.',millis)
from
(
select ((ts div 1000) div 86400) days, --number of whole days
       lpad(((ts div 1000) % 86400) div 3600, 2, 0) hours, --whole hours left
       lpad((((ts div 1000) % 86400) % 3600) div 60, 2, 0) minutes, --whole minutes left
       lpad((((ts div 1000) % 86400) % 3600) % 60, 2, 0) seconds, --seconds left
       (ts % 1000) as millis
  from your_data
)s

Result:

1 29:02:15:02.864 --29 whole days, 2 hours, 15 minutes, 2 seconds, 864 millis
2 0:00:00:17.259 --17 whole seconds and 259 millis
3 0:00:02:07.259 --two whole minutes, 7 seconds and 259 millis
4 0:00:00:01.272 --one whole second and millis
5 0:00:00:00.127 --we have only milliseconds

Now you can see the difference between this calculation and what from_unixtime returns.
For record id=1 the number of whole days is 29. Why from_unixtime returns 30 (for pattern 'D')? Because 29 whole days passed and we are 2 hrs 15 min 2 sec 864 mi in a new day 30. In other words, from_unixtime returns timestamp formatted and calculation in my query returns interval formatted, "day in a year" and "whole days passed from" are different things.

Hope, now it is as clear as a day.

See also similar question: https://stackoverflow.com/a/57497316/2700344

And if you need to convert bigint timestamp in milliseconds to string with milliseconds preserved (yyyy-MM-dd HH:mm:ss.SSS) use this:

select concat(from_unixtime(ts div 1000), '.', (ts  % 1000)) as timestamp_with_millis
  from (select bigint(2513702864)  as ts) s

Result:

1970-01-30 02:15:02.864

Convert seconds (string) to timestamp in Impala

Actually a quick and simple solution in the end:

to_timestamp(end_time, 'yyyy-MM-dd HH:mm:ss') end_time
CAST(table.seconds AS INT) sec
date_sub(end_time, INTERVAL sec seconds) start_time

SparkSQL - Difference between two time stamps in minutes

There are 2 ways to do it in Spark sql. You cast timestamp column to bigint and then subtract and divide by 60 are you can directly cast to unix_timestamp then subtract and divide by 60 to get result. I used pickup and dropoff column from dataframe above.(in pyspark/scala spark, bigint is long)

spark.sqlContext.sql("""select pickup, dropoff, (unix_timestamp(dropoff)-unix_timestamp(pickup))/(60) as diff from taxisub""").show()

OR

spark.sqlContext.sql("""select pickup, dropoff, ((bigint(to_timestamp(dropoff)))-(bigint(to_timestamp(pickup))))/(60) as diff from taxisub""").show()

Output:

+-------------------+-------------------+------------------+
|             pickup|            dropoff|              diff|
+-------------------+-------------------+------------------+
|2018-12-15 08:53:20|2018-12-15 08:57:57| 4.616666666666666|
|2018-12-15 08:03:08|2018-12-15 08:07:30| 4.366666666666666|
|2018-12-15 08:28:34|2018-12-15 08:33:31|              4.95|
|2018-12-15 08:37:53|2018-12-15 08:43:47|               5.9|
|2018-12-15 08:51:02|2018-12-15 08:55:54| 4.866666666666666|
|2018-12-15 08:03:47|2018-12-15 08:03:50|              0.05|
|2018-12-15 08:45:21|2018-12-15 08:57:08|11.783333333333333|
|2018-12-15 08:04:47|2018-12-15 08:29:05|              24.3|
|2018-12-15 08:01:22|2018-12-15 08:12:15|10.883333333333333|
+-------------------+-------------------+------------------+

---

Related Topics