Search code examples
rexcelr-xlsx

Timestamp changes when writing a R dataframe to an excel file depending upon UTC offset

I am trying to write a data frame into an excel file. The sample dataframe is as given below. As the timestamp is of class factor, I convert it into POSIXct format using the lubridate package. 

library(lubridate)
library(xlsx)
df=structure(list(ts = structure(c(5L, 8L, 9L, 1L, 6L, 7L, 4L, 2L, 3L),
 .Label = c("01.09.2016 10:56:56", "01.09.2016 11:04:37", 
"01.09.2016 12:03:59", "02.09.2016 08:47:01", "30.08.2016 08:27:28", 
"30.08.2016 16:08:56", "31.08.2016 07:38:43", "31.08.2016 10:26:53",
"31.08.2016 10:37:40"), class = "factor")), .Names = "ts", 
row.names = c(NA,-9L), class = "data.frame")
df$ts = as.POSIXct(strptime(df$ts, "%d.%m.%Y %H:%M:%S"))
write.xlsx(df, "output.xlsx", sheetName="output")

When I try to write the dataframe into an excel file using the write.xlsx command , I get an output where the timestamp is different from the original. R dataframe and excel output

It can be observed that the times are shifted by two hours. I live in a region belonging to the timezone UTC+02:00. Could this be the factor affecting the change? If so, is there a way to prevent excel from changing the time information as per the UTC offset?

Solution

  • The data in the R data frame is with the timezone CEST. When writing to excel, excel automatically changes the timezone to GMT which causes the time shift in excel. One workaround is by changing the timezone in R to GMT without changing the time data, by using force_tz.

    df$ts = as.POSIXct(strptime(df$ts, "%d.%m.%Y %H:%M:%S"))
Sys.setenv(TZ="")    
df$ts = force_tz(df$ts,tzone="GMT")
write.xlsx(df, "output.xlsx", sheetName="output")