Calculating Time Difference Between Two Columns

Calculating Time Difference between two columns

Firstly, this has nothing to do with lubridate.

Secondly, RStudio has let you down by screwing with the printing of the variable in the display window. If you enter CR_Date$hours in the command line window you will see it prints

#Time differences in secs
#[1]     0 36900

and head(CR_Date) gives:

#        pos1                pos2      hours
#1 2014-07-01 2014-07-01 00:00:00     0 secs
#2 2014-07-01 2014-07-01 10:15:00 36900 secs

Either of which would have tipped you off as to what it is displaying.

As @Victorp suggests, difftime is the way to resolve this:

CR_Date$hours <- with(CR_Date, difftime(pos2,pos1,units="hours") )
CR_Date

#        pos1                pos2       hours
#1 2014-07-01 2014-07-01 00:00:00  0.00 hours
#2 2014-07-01 2014-07-01 10:15:00 10.25 hours

Calculate TIMESTAMP_DIFF between two columns in BigQuery

SELECT TIMESTAMP_DIFF(end_time, start_time, MINUTE) AS minutes
FROM your_table 

Calculate time difference in hours between two dates and times

To get the total hours between two dates, just subtract and apply the proper format:

Sub INeedADate()
    [c1] = [b1] - [a1]
    Range("C1").NumberFormat = "[hh]:mm"
End Sub

How to calculate average time difference in 2 columns in the given table?

You can just take the difference and average:

select avg(datetime2 - datetime1)
from t;

avg() ignores NULL values.

Here is a db<>fiddle.

How to calculate time difference of two columns with a lag

You can create a datetime column for both pickup and dropoff and for each hack_license calculate the difference in time between the current pickup time and previous drop off time.

library(dplyr)
library(lubridate)

data <- data %>%
          mutate(pickup_datetime = ymd_hms(pickup_datetime), 
                 dropoff_datetime = ymd_hms(dropoff_datetime)) %>%
           group_by(hack_license) %>%
           mutate(waiting_time_in_secs = as.numeric(difftime(pickup_datetime, 
                                lag(dropoff_datetime), units = 'secs')))
data
#   hack_license                     pickup_datetime     dropoff_datetime    waiting_time_in_secs
#   <chr>                            <dttm>              <dttm>                             <dbl>
# 1 303F79923DA5DA7A10DF15E2D91CDCF7 2013-12-31 23:01:07 2013-12-31 23:20:33                   NA
# 2 697ABFCDF7E7C77A01183C857132F2A4 2013-12-31 23:04:00 2013-12-31 23:28:00                   NA
# 3 697ABFCDF7E7C77A01183C857132F2A4 2013-12-31 23:31:00 2013-12-31 23:33:00                  180
# 4 697ABFCDF7E7C77A01183C857132F2A4 2013-12-31 23:40:00 2013-12-31 23:48:00                  420
# 5 ABE23CA71E2DE84972281BA1C70B6EBB 2013-12-31 23:16:39 2013-12-31 23:22:29                   NA
# 6 ABE23CA71E2DE84972281BA1C70B6EBB 2013-12-31 23:24:05 2013-12-31 23:28:37                   96
# 7 BA83D7C383EAA4F9D78A1A8B83CB3E92 2013-12-31 23:09:10 2013-12-31 23:21:24                   NA
# 8 BA83D7C383EAA4F9D78A1A8B83CB3E92 2013-12-31 23:26:26 2013-12-31 23:36:54                  302
# 9 D476A1872F1F6594BD638C274483ED06 2013-12-31 23:13:00 2013-12-31 23:20:00                   NA
#10 D476A1872F1F6594BD638C274483ED06 2013-12-31 23:22:00 2013-12-31 23:27:00                  120

Calculate the difference in time between two dates and add them to a new column

You need to make some changes in your code.

First and foremost, don't use $ in dplyr pipes. Pipes (%>%) were created to avoid using df$column_name everytime you want to use variable from the dataframe. Using $ can have unintended consequences when grouping the data or using rowwise as you can see in your case.

Secondly, difftime is vectorised so no need of rowwise here.

Finally, if you want time difference in minutes you should change the values to POSIXct type and not dates. Try the following -

library(dplyr)

df <- df %>%
  mutate(trip_duration = difftime(as.POSIXct(`end time`), 
                                  as.POSIXct(`start time`), units = "mins"))

---

Related Topics