search

codeinthehole / rpostgresql

Automatically exported from code.google.com/p/rpostgresql
3 stars 0 forks source link

Carrying timezones around #79

Open etiennebr opened 8 years ago

etiennebr commented 8 years ago

Related to #55

I don't know how active this is, but I have trouble carrying timezones between R and Postgres

require(lubridate)

Sys.setenv(TZ="EST")
x <- data.frame(
  d = ymd(c("20151105", "20151106"), tz="UTC")
)
tz(x$d)
with_tz(x$d, "EST")

dbWriteTable(con, "temp", x, row=FALSE)

# timezone is loaded in the database with local timezone :(
dbGetQuery(con, "select d::text from temp;")

y <- dbReadTable(con, "temp")
# no timezone :(
tz(y$d)
with_tz(y$d, "EST")

dbRemoveTable(con, "temp")

I've used a function

date_pg <- function(x=Sys.time()) format(x, "'%Y%m%dT%H%M %z'::timestamptz")

to compose queries, but reading tables is the most problematic as it requires to set timezone for each POSIXct column.