将R数据帧写入excel文件时，时间戳会更改，具体取决于UTC偏移量 [英] Timestamp changes when writing a R dataframe to an excel file depending upon UTC offset

问题描述

我正在尝试将数据帧写入excel文件.示例数据帧如下所示.由于时间戳属于factor类，因此我使用lubridate软件包将其转换为POSIXct格式.

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")

当我尝试使用write.xlsx命令将数据帧写入excel文件时，得到的输出时间戳不同于原始时间戳.

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.

可以观察到时间偏移了两个小时.我居住在时区UTC + 02:00所在的区域.这可能是影响变更的因素吗?如果是这样，是否有一种方法可以防止excel根据UTC偏移量更改时间信息?

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?

推荐答案

R数据帧中的数据具有时区CEST.在写入excel时，excel会自动将时区更改为GMT，这会导致excel中的时间偏移. 一种解决方法是通过使用force_tz将R中的时区更改为GMT而不更改时间数据.

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")

这篇关于将R数据帧写入excel文件时，时间戳会更改，具体取决于UTC偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！