Search code examples
rtimezoneposixcttimezone-offsetstrptime

Convert character and UTC offset to POSIXct in R

I have the following data in R:

times <- c("2015-03-08 00:00:00", "2015-03-08 01:00:00", "2015-03-08 02:00:00", "2015-03-08 03:00:00")
utcOffsets <- c(2, 1, 1, 1)
mydata <- data.frame(time=times, utcOffset=utcOffsets)

The utcOffset column in my table indicates the time zone offset of each data value. When I tried to convert my data to POSIXct using the strptime function, I have a problem. The time "2015-03-08 01:00:00" is converted to NA for some reason.

as.POSIXct(strptime(mydata$time, "%Y-%m-%d %H:%M:%S"))

[1] "2015-03-08 00:00:00 MST" "2015-03-08 01:00:00 MST" NA
[4] "2015-03-08 03:00:00 MDT"

I suspect that there is some problem with the daylight saving time hour and that I have to specify the time zone in the strptime function. If the time zone was fixed for all my data, the following code would work for me:

as.POSIXct(strptime(mydata$time, "%Y-%m-%d %H:%M:%S", tz="Europe/Prague"))

[1] "2015-03-08 00:00:00 CET" "2015-03-08 01:00:00 CET" "2015-03-08 02:00:00 CET" [4] "2015-03-08 03:00:00 CET"

However in my data the time zone is specified in the utcOffset column of my table, for example utcOffset=1 means GMT+1 or utcOffset=2 means GMT+2. When I tried using a time zone code in the format "GMT+n", I get a warning:

as.POSIXct(strptime(mydata$time, "%Y-%m-%d %H:%M:%S", tz="GMT+1"))

[1] "2015-03-08 00:00:00 GMT" "2015-03-08 01:00:00 GMT" "2015-03-08 02:00:00 GMT"
[4] "2015-03-08 03:00:00 GMT"
Warning messages:
1: In strptime(mydata$time, "%Y-%m-%d %H:%M:%S", tz = "GMT+1") :
  unknown timezone 'GMT+1'
2: In as.POSIXct.POSIXlt(strptime(mydata$time, "%Y-%m-%d %H:%M:%S",  :
  unknown timezone 'GMT+1'
3: In as.POSIXlt.POSIXct(x, tz) : unknown timezone 'GMT+1'

How can I convert my data to POSIXct using the utcOffset information in my table?

Solution

  • Thanks to the comment of oshun I found an answer to my problem:

    times <- c("2015-03-08 00:00:00", "2015-03-08 01:00:00", "2015-03-08 02:00:00", "2015-03-08 03:00:00")
utcOffsets <- c(2, 1, 1, 1)
mydata <- data.frame(time=times, utcOffset=utcOffsets)

# convert the times to POSIXct (use UTC as default timezone)
localTimes <- as.POSIXct(times, format="%Y-%m-%d %H:%M:%S", tz="GMT")
mydata$UTCTime <- localTimes + utcOffsets*3600