Subtracting Columns of Time


I'm having trouble subtracting two different columns of time. The output that I am receiving is wildly off. For example:

started_at = 2020-05-27 10:03:52
ended_at = 2020-05-27 10:16:49

I used this to convert the columns:

bike_data[["started_at"]] <-as.POSIXct(bike_data[["started_at"]],format = "%Y-%m-%d %H:%M:%S")
bike_data[["ended_at"]] <-as.POSIXct(bike_data[["ended_at"]],format = "%Y-%m-%d %H:%M:%S")

However, when I subtract, the value I get for the example given above is 13,623 seconds, when it should be around 780 seconds.

bike_data$duration <- bike_data$ended_at - bike_data$started_at

I've also tried to use the ymd_hms() function of lubridate to convert the columns but I run into the same issue.

What am I doing wrong?

I'm assuming your downloaded data is in string form. Try this:

bike_data_raw <- tibble(started_at = "2020-05-27 10:03:52",
                        ended_at =  "2020-05-27 10:16:49")

bike_data <- bike_data_raw %>% 
  mutate(started_at = as.POSIXct(started_at,format = "%Y-%m-%d %H:%M:%S"),
         ended_at = as.POSIXct(ended_at,format = "%Y-%m-%d %H:%M:%S"))

bike_data$ended_at - bike_data$started_at
#> Time difference of 12.95 mins

# or to add a new column
bike_data <-  bike_data %>% mutate(duration = ended_at - started_at)
#> # A tibble: 1 x 3
#>   started_at          ended_at            duration  
#>   <dttm>              <dttm>              <drtn>    
#> 1 2020-05-27 10:03:52 2020-05-27 10:16:49 12.95 mins
1 Like

Wow, thank you so much. It worked! If you don't mind explaining, why didn't mine work? What is it about the mutate function that allowed for the accurate result when, on paper, the values are the same?

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.