How to calculate the time difference between two columns?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.