CODEWITHSUNDEEP
rcalculated-columnsdate-arithmetic
BB.squared
BB.squared

Reputation: 113

R: Calculating total hours from Dates and Times

First time question and new to R.

I am pulling data from a SQL server and putting into a R data table (SAP). I am trying to calculate the total hours from 4 columns (StartDate, FinDate, StartTime, FinTime).

DataTable

I have tried this to calcute from datatable (SAP), but not getting what I want.

SAP$hours <- with(SAP,
                  difftime(c(ActStartDate, ActStartTime), 
                           c(ActFinDate, ActFinTime),
                           units = "hours") )

I would like to have the total hours added to the data Table or a vector assigned the total hours.

This is how I would do in excel:

Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24

Upvotes: 1

Views: 1339

Answers (2)

BB.squared
BB.squared

Reputation: 113

Thank you all. I ended up doing this with your inputs and it worked. 

    # make it a data table
    SAP <- data.table(SAP)

    # only select some columns of interest
    SAP <- SAP[, .(Equipment, Order, ActStartDate, ActStartTime, ActFinDate, ActFinTime)]

    # generate start / end as POSIX, 
    # this code assumes that start date from SAP is always like 2018-05-05
    # so 10 chars as YYYY-MM-DD
    # if needed add time zone information, e.g as.POSIXct(..., tz = 'UTC')
    SAP[, start := as.POSIXct(paste0(substring(ActStartDate, 1, 10), ActStartTime))]
    SAP[, end := as.POSIXct(paste0(substring(ActFinDate, 1, 10), ActFinTime))]

    # calculate duration
    SAP[, duration := difftime(end, start, units = "hours")]

Upvotes: 0

SmitM
SmitM

Reputation: 1376

You could do something like this:

#sample data:
df <- data.frame(startdate = c("2018-08-23 00.00.00"), 
                 enddate = c("2018-08-24 00.00.00"), 
                 starttime = c("23:00:00"), 
                 endtime = c("23:30:00"))

#This will first combine date(after extracting the date part) and time and
#then convert it to a date time object readable by R. 
df$sdt <- as.POSIXct(paste(substr(df$startdate, 1, 10), 
                           df$starttime, 
                           sep = " "), 
                     format = "%Y-%m-%d %H:%M:%S")    

#Same for end date time
df$edt <- as.POSIXct(paste(substr(df$enddate, 1, 10), 
                           df$endtime, 
                           sep = " "), 
                     format = "%Y-%m-%d %H:%M:%S")

df$diff <- difftime(df$edt, df$sdt, units = "hours")

Upvotes: 1

Related Questions