postgresql - RpostgreSQL 中的时区和 POSIXct 处理

Question

我在 RPostgreSQL 中处理日期时间时遇到问题。具体来说，它涉及具有 UTC 时区的 POSIXct 对象，在上传到 postgres 数据库期间自动调整为夏令时。一个简单的例子:

library(RPostgreSQL)

example = data.frame(date=as.POSIXct('2016-08-14 15:50:00',tz='UTC'))

con = dbConnect(dbDriver("PostgreSQL"), 
                dbname="mydb",
                host="localhost",
                port="5432",
                user="me",
                password="password")

dbWriteTable(con,name=c('myschema','mytable'),example,overwrite=T)

example2 = dbReadTable(con,name=c('myschema','mytable'))

dbDisconnect(con)

example2 # 2016-08-14 14:50:00

在这种情况下，时间导出为 15:50，但读回为 14:50，这表明已应用英国夏令时夏令时。我尝试将我的系统设置调整为 UTC，使用 Sys.setenv(TZ='UTC') 将 R 中的时区设置为 UTC，并使用 SET timezone 将 Postgres 中的时区设置为 UTC到“UTC”，一切都无济于事。

有谁知道转换过程中可能发生的位置以及 dbWriteTable 从哪里获取时区？对于其他可能需要调整的设置，是否有任何建议？

Answer 1

我也遇到了 RPostgreSQL 的奇怪问题(UTC 以某种方式成为 UTC -4:00)。但是使用 RPostgres 似乎没问题。

注意R中显示的时区是本地时间。如果在运行 R 代码和 SET TIME ZONE 'GMT'; 后进入 PostgreSQL(例如，psql)，您会看到 2016-08-14 R 中显示的 16:50:00 实际上在数据库中存储为 2016-08-14 15:50:00 UTC。换句话说，在我的示例中，R 中显示的 2016-08-14 16:50:00 对于 rubbish_alt 是正确的。

crsp=# SET TIME ZONE 'GMT';
SET
crsp=# SELECT * FROM rubbish;
 row.names |          date          
-----------+------------------------
 1         | 2016-08-14 19:50:00+00
(1 row)

crsp=# SELECT * FROM rubbish_alt;
          date          
------------------------
 2016-08-14 15:50:00+00
(1 row)

crsp=# \d rubbish
              Table "public.rubbish"
  Column   |           Type           | Modifiers 
-----------+--------------------------+-----------
 row.names | text                     | 
 date      | timestamp with time zone | 

crsp=# \d rubbish_alt
          Table "public.rubbish_alt"
 Column |           Type           | Modifiers 
--------+--------------------------+-----------
 date   | timestamp with time zone | 

R 代码(注意使用 Sys.setenv(PGHOST="myhost", PGDATABASE="mydb") 等在其他地方生成此 reprex() 代码)为任何人奔跑):

Sys.setenv(TZ='Europe/London') 

# With RPostgreSQL ----
library(RPostgreSQL)
#> Loading required package: DBI

example <- data.frame(date=as.POSIXct('2016-08-14 15:50:00', tz='UTC'))

con = dbConnect(PostgreSQL())

dbWriteTable(con, 'rubbish', example, overwrite=TRUE)
#> [1] TRUE

example2 <- dbReadTable(con, name="rubbish")

dbDisconnect(con)
#> [1] TRUE

example2 
#>                  date
#> 1 2016-08-14 20:50:00

# With RPostgres ----
library(RPostgres)

example <- data.frame(date=as.POSIXct('2016-08-14 15:50:00', tz='UTC'))

con = dbConnect(Postgres())

dbWriteTable(con, 'rubbish_alt', example, overwrite=TRUE)

example2 <- dbReadTable(con, name="rubbish_alt")

dbDisconnect(con)

example2 
#>                  date
#> 1 2016-08-14 16:50:00
example2$date[1]
#> [1] "2016-08-14 16:50:00 BST"