Часовой пояс и обработка POSIXct в RpostgreSQL

У меня проблема с обработкой даты и времени в RPostgreSQL. В частности, это относится к объектам POSIXct с часовым поясом UTC, который автоматически настраивается на переход на летнее время во время загрузки в базу данных 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, установить часовой пояс в R на UTC, используя Sys.setenv(TZ='UTC'), и установить часовой пояс в Postgres на UTC, используя SET timezone TO 'UTC', все безрезультатно.

Кто-нибудь знает, где в процессе может происходить преобразование и откуда dbWriteTable берет свой часовой пояс? Есть ли какие-либо предложения по другим настройкам, которые могут потребовать корректировки?


postgresql rpostgresql
person Sam Weber    schedule 15.12.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward

У меня также возникают странные проблемы с RPostgreSQL (с UTC каким-то образом UTC -4:00). Но с использованием RPostgres все в порядке.

Обратите внимание, что часовой пояс, отображаемый в R, соответствует местному времени. Если вы войдете в PostgreSQL (скажем, psql) после запуска кода R и SET TIME ZONE 'GMT';, вы увидите, что 2016-08-14 16:50:00, отображаемый в R, фактически хранится в базе данных как 2016-08-14 15:50:00 UTC. Другими словами, 2016-08-14 16:50:00, отображаемый в R, соответствует 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"
person Ian Gow    schedule 16.02.2018