Interconverting POSIXct and numeric in R

Question

I'm importing data from Excel and then trying to manipulate dates and times in R and it's giving me SUCH A HEADACHE. In the Excel file, one column contained a date and time, and another column contained a different time for that same day. The data in R looks basically like this example:

 mydata <- data.frame(DateTime1 = as.POSIXct(c("2014-12-13 04:56:00",
                                         "2014-12-13 09:30:00",
                                         "2014-12-13 11:30:00",
                                         "2014-12-13 13:30:00"),
                                       origin = "1970-01-01", tz = "GMT"),
                      Time2 = c(0.209, 0.209, 0.715, 0.715))

I'd like to have a new column in POSIXct format with the date and the 2nd time, and I can't get that to work. I've tried:

 mydata$DateTime2 <- as.POSIXct(as.numeric(as.Date(mydata$DateTime1)
                            + mydata$Time2), origin = "1970-01-01",
                           tz = "GMT")

but that gives me dates and times close to 1/1/1970.

This is more convoluted, but one thing that has worked in other similiar situations that I've also tried is:

 library(lubridate)
 mydata$DateTime2 <- ymd_hms(format(as.POSIXct(as.Date(mydata$DateTime1) + 
                                                mydata$Time2,
                      origin = "1899-12-30", tz = "GMT")))

but that gives me dates and times that are off by 8 hours. That time difference makes me think that the problem is the time zone since I'm on Pacific Standard Time, but I set it to GMT both in the input data and when trying to convert! What gives? I'm hesitant to just add 8 hours to everything because of daylight savings time complications.

Really, both of the attempts I'm listing here seem to have problems with interconversion, i.e., if you start with a POSIXct object and convert it to numeric and then convert it back again to POSIXct, you should end up back where you started and you don't. Similarly, if you start with the time zone being GMT and then you add something that also is set to have the time zone as GMT, then you shouldn't have a problem with things somewhere mysteriously getting converted to the system time zone.

Advice?

Examine unclass(DateTime1) before and after converting. It should be the same underlying integers (technically numeric) before and after. If that's so, it's just a matter of setting the right time zone/origin. — MichaelChirico
Thank you for the suggestion, but I'm not seeing any difference between including unclass(... and not including it. — shirewoman2
I may be misunderstanding, but why couldn't you just directly add Time2 to DateTime1. I'm not sure of the units of Time2, but let's say it's days, just for illustration. Then, mydata$DateTime2 = mydata$DateTime1 + mydata$Time2*24*60*60 (because POSIXct is in seconds, so we convert Time2 to seconds). — eipi10
The problem is that I'm not adding the second clock time to the first. Instead, I need the date and only the date in DateTime1 and then I need to add to that the clock time in Time2. — shirewoman2
Ah, okay. But then why does Time2 look like a decimal number rather than a time? — eipi10

shirewoman2 shirewoman2 · Accepted Answer · 2016-05-06T21:25:17

I found an answer based on chris holbrook's answer here: How do you convert dates/times from one time zone to another in R?

This worked:

 mydata$DateTime2 <- as.POSIXct(as.Date(mydata$DateTime1) +
                                 mydata$Time2)
 attributes(mydata$DateTime2)$tzone <- "GMT"

@MichaelChirico and I were correct that the time zone was the problem. I'm still not sure why, but the time zone for DateTime2 was apparently PST. It didn't list "PST" when I checked str(mydata$DateTime2), but based on the time difference, it must have been, in fact, PST until I set the attributes. Crazy. It did that even though DateTime1 was GMT.

Interconverting POSIXct and numeric in R

1 Answers