How to find the time difference between two POSIXct columnns

[Jonathan_Shmulovich]

Hello, I have a dataframe, df. I have two columns of datatype POSIXct: df$started_at and df$ended_at. I want to make a column, df$ride_length, that tells the difference between these two.

I have the library(dplyr) loaded, and tried to do this, but it didn't work:

df %>%
mutate(df$ride_length = df$ended_at - df$started_at)

Examples of the format are:
"2022-01-13 11:59:47"
"2022-01-10 08:41:56"
"2022-01-25 04:53:40"
"2022-01-04 00:18:04"

How would you go about this? Thanks as always community!

[FJCC]

You can do a simple subtraction to get a difftime value. Note I used the bare column names in the mutate() function.

library(lubridate)
library(dplyr)
DF <- data.frame(Start = ymd_hms("2023-01-12 03:45:15","2023-02-03 21:06:43"),
                 End = ymd_hms("2023-01-12 05:17:05","2023-02-04 19:34:16"))
DF
#>                 Start                 End
#> 1 2023-01-12 03:45:15 2023-01-12 05:17:05
#> 2 2023-02-03 21:06:43 2023-02-04 19:34:16
DF <- DF |> mutate(Length = End - Start)
DF
#>                 Start                 End          Length
#> 1 2023-01-12 03:45:15 2023-01-12 05:17:05  1.530556 hours
#> 2 2023-02-03 21:06:43 2023-02-04 19:34:16 22.459167 hours

str(DF)
#> 'data.frame':    2 obs. of  3 variables:
#>  $ Start : POSIXct, format: "2023-01-12 03:45:15" "2023-02-03 21:06:43"
#>  $ End   : POSIXct, format: "2023-01-12 05:17:05" "2023-02-04 19:34:16"
#>  $ Length: 'difftime' num  1.53055555555556 22.4591666666667
#>   ..- attr(*, "units")= chr "hours"

^{Created on 2023-02-14 with reprex v2.0.2}

[Jonathan_Shmulovich]

Hey thanks, This worked!

I'm going to look around to see if the format to minutes for mine, but ideally I would like to make it "X hours Y Minutes" while being the same datatype...

@FJCC

t)

[FJCC]

You can store the difftime as seconds but display it as h:m:s using the hms package.

library(lubridate)
library(dplyr)
library(hms)
DF <- data.frame(Start = ymd_hms("2023-01-12 03:45:15","2023-02-03 21:06:43"),
                  End = ymd_hms("2023-01-12 05:17:05","2023-02-04 19:34:16"))
DF
                Start                 End
1 2023-01-12 03:45:15 2023-01-12 05:17:05
2 2023-02-03 21:06:43 2023-02-04 19:34:16
DF <- DF |> mutate(Length = as_hms(End - Start))
DF
                Start                 End   Length
1 2023-01-12 03:45:15 2023-01-12 05:17:05 01:31:50
2 2023-02-03 21:06:43 2023-02-04 19:34:16 22:27:33

 str(DF)
'data.frame':	2 obs. of  3 variables:
 $ Start : POSIXct, format: "2023-01-12 03:45:15" "2023-02-03 21:06:43"
 $ End   : POSIXct, format: "2023-01-12 05:17:05" "2023-02-04 19:34:16"
 $ Length: 'hms' num  01:31:50 22:27:33
  ..- attr(*, "units")= chr "secs"