Timestamp conversion in R and calculating Time Difference between 2 Columns of different DFs

Question

I need to calculate time difference in minutes/hours/days etc between 2 Date-Time columns of two dataframes, please find the details below

df1 <- data.frame (Name = c("Aks","Bob","Caty","David"),
                   timestamp = c("Mon Apr 1 14:23:09 1980", "Sun Jun 12 12:10:21 1975", "Fri Jan 5 18:45:10 1985", "Thu Feb 19 02:26:19 1990"))

df2 <- data.frame (Name = c("Aks","Bob","Caty","David"),
                   timestamp = c("Apr-01-1980 14:28:00","Jun-12-1975 12:45:10","Jan-05-1985 17:50:30","Feb-19-1990 02:28:00"))

I am facing problem in converting df1$timestamp and df2$timestamp , here POSIXct & as.Date are not working getting error - non numeric argument to binary operator

I need to calculate time diff in mins/hrs or days

Answer 1

One approach is strptime and indicate the appropriate directives in the datetime format:

df1$timestamp2 <- strptime(df1$timestamp, "%a %b %d %H:%M:%S %Y")
df2$timestamp2 <- strptime(df2$timestamp, "%b-%d-%Y %H:%M:%S")

In this case, you have:

• %a abbreviated weekday name
• %b abbreviated month name
• %d day of the month
• %H hour, 24-hour clock
• %M minute
• %S second
• %Y year including century

Then you can use difftime to get the difference, and specify the units (in this case, difference expressed in hours):

difftime(df1$timestamp2, df2$timestamp2, units = "hours")

Output

Time differences in hours
[1] -0.08083333 -0.58027778  0.91111111 -0.02805556

Answer 2

If locale-setting prevent correct reading, try:

# Store current locale
orig_locale <- Sys.getlocale("LC_TIME")
Sys.setlocale("LC_TIME", "C")
# Convert to posix-timestamp
df1$timestamp <- as.POSIXct( df1$timestamp, format = "%a %b %d %H:%M:%S %Y")
df2$timestamp <- as.POSIXct( df2$timestamp, format = "%b-%d-%Y %H:%M:%S")
# Restore locale
Sys.setlocale("LC_TIME", orig_locale)
# Calculate difference
df2$timestamp - df1$timestamp
# Time differences in mins
# [1]   4.850000  34.816667 -54.666667   1.683333