How to properly handle timezone when passing POSIXct objects between R and Postgres DBMS?

Question

I am struggling to understand what exactly happens behind the scenes when passing POSIXct objects between R and Postgres using RPostgreSQL. In the following example, I define two timestamp fields: one with a timezone the other one without. However, it appears that they are treated exactly the same when passing POSIXct objects via dbWriteTable and dbReadTable.

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host = "127.0.0.1", port = "5432", user= "postgres",
                 dbname = "test_db")

q <- "
CREATE TABLE test_table
(
  dttm timestamp without time zone,
  dttmtz timestamp with time zone
)"
dbSendQuery(con, q)

# using timezone CET
dttm <- as.POSIXct("2016-01-01 10:20:10", tz="CET")
df <- data.frame(dttm = dttm, dttmtz = dttm)
dbWriteTable(con, "test_table", df, overwrite=FALSE, append=T, row.names=0)

# using timezone UTC    
dttm <- as.POSIXct("2016-01-01 14:20:10", tz="UTC")
df <- data.frame(dttm = dttm, dttmtz = dttm)
dbWriteTable(con, "test_table", df, overwrite=FALSE, append=T, row.names=0)

df2 <- dbReadTable(con, "test_table")

Both fields come out exactly the same. It appears as if the timezones are completely discarded.

df2$dttm
[1] "2016-01-01 10:20:10 CET" "2016-01-01 14:20:10 CET"

df2$dttmtz
"2016-01-01 10:20:10 CET" "2016-01-01 14:20:10 CET"

QUESTIONS:

1. What exactly goes on behind the scenes?
2. How can I properly pass the POSIXct's timezone back and forth?

Answer 1

I think you've pointed out a bug in RPostgreSQL: it does not seem to be getting time zone from R for POSIXct objects. Timezone information can be passed correctly to PostgreSQL by formatting timestamps as character with offset from UTC (see example at bottom of this answer; added 2018-09-21). But first, here's an illustration of the apparent bug:

Modifying your code:

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,  port = "5432", user= "postgres",
                 dbname = "test")

# timestamps in three different time zones
dt1 <- as.POSIXct("2016-01-01 10:20:10", tz="US/Eastern")
dt2 <- as.POSIXct("2016-01-01 10:20:10", tz="UTC")
dt3 <- as.POSIXct("2016-01-01 10:20:10", tz="Asia/Tokyo")
df <- data.frame(dt1=dt1, dt2=dt2, dt3=dt3)

q <- "
CREATE TABLE test_table
(
  dt1 timestamp with time zone,
  dt2 timestamp with time zone,
  dt3 timestamp with time zone,
  PRIMARY KEY (dt1)
)"
dbSendQuery(con, q)

dbWriteTable(con, "test_table", df, overwrite=FALSE, append=T, row.names=0)

df2 <- dbReadTable(con, "test_table")

note that all three timestamps are equal timezones not handled correctly

df2$dt1

"2016-01-01 10:20:10 EST"

df2$dt2

"2016-01-01 10:20:10 EST"

df2$dt3

"2016-01-01 10:20:10 EST"

And same is true in postgres - as seen in pgadmin here

This suggests postgres is not getting timezone from R

Note that if we manually change one time zone in test_table (e.g., first record in pgadmin)

eg,

and fetch

df2 <- dbReadTable(con, "test_table")

then the timezone is correctly handled

df2$dt1

"2016-01-01 05:20:10 EST"

df2$dt2

"2016-01-01 10:20:10 EST"

df2$dt3

"2016-01-01 10:20:10 EST"

So this suggets that RPostgreSQL is not correctly passing time zone information to postgres but that RPostgreSQL is correctly getting time zone information from postgres.

---

answer to the original question

To pass a timestamp with timezone from R to Postgres using RPostgreSQL, just format it as a character string with the offset from UTC (e.g., "2016-01-01 10:20:10-0500"; e.g., use format and then pass it to Postgres, same as above.

E.g.:

#convert POSIXct to character with offset from UTC
df$dt1 <- format(df$dt1, format = "%Y-%m-%d %H:%M:%OS%z")
df$dt2 <- format(df$dt2, format = "%Y-%m-%d %H:%M:%OS%z")
df$dt3 <- format(df$dt3, format = "%Y-%m-%d %H:%M:%OS%z")

##> df
##                       dt1                      dt2                      dt3
##1 2016-01-01 10:20:10-0500 2016-01-01 10:20:10+0000 2016-01-01 10:20:10+0900

q <- "
CREATE TABLE test_table2
(
  dt1 timestamp with time zone,
  dt2 timestamp with time zone,
  dt3 timestamp with time zone,
  PRIMARY KEY (dt1)
)"
dbSendQuery(con, q)

dbWriteTable(con, "test_table2", df, overwrite=FALSE, append=T, row.names=0)

df3 <- dbReadTable(con, "test_table2")

#Note that times are now correct (in local time zone)
##> df3$dt1
##[1] "2016-01-01 10:20:10 EST"
##> df3$dt2
##[1] "2016-01-01 05:20:10 EST"
##> df3$dt3
##[1] "2015-12-31 20:20:10 EST"