CODEWITHSUNDEEP
r
Bobo022
Bobo022

Reputation: 11

How to calculate the time difference between two columns?

I have a beginner question. I have the following data:

started_at ended_at duration
10/08/2021 17:15 10/08/2021 17:22 NA
10/08/2021 17:23 10/08/2021 17:39 NA
21/08/2021 02:34 21/08/2021 02:50 NA
21/08/2021 06:52 21/08/2021 07:08 NA
19/08/2021 11:55 19/08/2021 12:04 NA
19/08/2021 12:41 19/08/2021 12:47 NA

I would like to calculate the time difference and add that to the duration column.

Upvotes: 1

Views: 1637

Answers (3)

Cheshiremoe
Cheshiremoe

Reputation: 1080

The Brute method:

If your using a language with Date objects convert your Date/Time objects to milliseconds.

Then

diff = endDateMilliseconds - startDateMilliseconds

You might have some library to convert back to time but you can do it manually

durationInMinutes = diff / 60000

If you need days & minutes you would need to use remainder %.

days = diff / (1440 * 60000)  
mins = (diff % (1440 * 60000))/60000

Probably better ways to do this depending on language your using.

Upvotes: 1

Bobo022
Bobo022

Reputation: 11

Solved

library(lubridate)

data %>% 
  mutate(ended_at = dmy_hm(ended_at),
         started_at = dmy_hm(started_at)) %>% 
  mutate(duration = as.numeric(difftime(ended_at, started_at, units = "mins")))
started_at ended_at duration
2021-08-10 17:15:00 2021-08-10 17:22:00 7
2021-08-10 17:23:00 2021-08-10 17:39:00 16
2021-08-21 02:34:00 2021-08-21 02:50:00 16
2021-08-21 06:52:00 2021-08-21 07:08:00 16
2021-08-19 11:55:00 2021-08-19 12:04:00 9
2021-08-19 12:41:00 2021-08-19 12:47:00 6

Upvotes: 0

TarJae
TarJae

Reputation: 78917

We could use difftime:

library(lubridate)
library(dplyr)

df %>%
  mutate(across(contains("at"), dmy_hm), # you don't need this if your colums is already in datetime format
         duration=difftime(ended_at, started_at))

Output:

           started_at            ended_at duration
1 2021-08-10 17:15:00 2021-08-10 17:22:00   7 mins
2 2021-08-10 17:23:00 2021-08-10 17:39:00  16 mins
3 2021-08-21 02:34:00 2021-08-21 02:50:00  16 mins
4 2021-08-21 06:52:00 2021-08-21 07:08:00  16 mins
5 2021-08-19 11:55:00 2021-08-19 12:04:00   9 mins
6 2021-08-19 12:41:00 2021-08-19 12:47:00   6 mins

Upvotes: 3

Related Questions