3

Why do I lose the change of timezone of a POSIXct object when exporting to xslx?

data:

t<- structure(list(created_at = structure(c(1502132771, 1502132774, 1502132827, 1502132855, 1502132877), class = c("POSIXct", "POSIXt"), tzone = "UTC")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,                                                                                                                   -5L), .Names = "created_at")

Create new variable with modified time zone:

t$created_atGMT2 <- t$created_at
t$created_atGMT2 <-  as.POSIXct(as.integer(t$created_at),origin="1970-01-01",tz="Europe/Vienna")
#alternatively attr(t$created_atGMT2, "tzone") <- "Europe/Vienna"

head(t)

1 2017-08-07 19:06:11 2017-08-07 21:06:11
2 2017-08-07 19:06:14 2017-08-07 21:06:14
3 2017-08-07 19:07:07 2017-08-07 21:07:07
4 2017-08-07 19:07:35 2017-08-07 21:07:35
5 2017-08-07 19:07:57 2017-08-07 21:07:57

Export data.frame to xlsx

library(xlsx)
write.xlsx2(x=t, file = "file.xlsx")

Import xlsx to R

library(readxl)
t1<-read_xlsx("file.xlsx") 

head(t1)

1 2017-08-07 19:06:11 2017-08-07 19:06:11
2 2017-08-07 19:06:14 2017-08-07 19:06:14
3 2017-08-07 19:07:07 2017-08-07 19:07:07
4 2017-08-07 19:07:35 2017-08-07 19:07:35
5 2017-08-07 19:07:57 2017-08-07 19:07:57
zoowalk
  • 2,018
  • 20
  • 33
  • Possible duplicate of [How to prevent write.csv from changing POSIXct, dates and times class back to character/factors?](https://stackoverflow.com/questions/23295571/how-to-prevent-write-csv-from-changing-posixct-dates-and-times-class-back-to-ch) – Z.Lin Aug 30 '17 at 14:30
  • MS Excel does not have time zone handling, so it seems the xlsx package is writing POSIX timestamps in UTC – Chris Holbrook Aug 30 '17 at 15:10
  • see also https://stackoverflow.com/questions/39413379/timestamp-changes-when-writing-a-r-dataframe-to-an-excel-file-depending-upon-utc – Chris Holbrook Aug 30 '17 at 15:11

0 Answers0