Timezone and POSIXct handling in RpostgreSQL

Question

I'm having an issue with datetime handling in RPostgreSQL. Specifically it relates to POSIXct objects with a UTC timezone being automatically adjusted to daylight saving during upload to a postgres database. A simple example:

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

In this case the time is exported as 15:50 but read back in as 14:50, suggesting that British Summer Time daylight saving has been applied. I've tried adjusting my system settings to UTC, setting the timezone in R to UTC using Sys.setenv(TZ='UTC') and setting the timezone in Postgres to UTC using SET timezone TO 'UTC', all to no avail.

Does anybody know where in the process the conversion is likely to be happening and where dbWriteTable is taking its timezone from? Are there any suggestions on other settings that might need adjusting?

Ian Gow · Answer 1 · 2018-02-16T20:11:53.927

I also get strange issues with RPostgreSQL (with UTC somehow being UTC -4:00). But things seem fine using RPostgres.

Note that the time zone displayed in R is in local time. If you go into PostgreSQL (say, psql) after running the R code and SET TIME ZONE 'GMT';, you see that the 2016-08-14 16:50:00 displayed in R is actually stored in the database as 2016-08-14 15:50:00 UTC. In other words, 2016-08-14 16:50:00 displayed in R is correct for rubbish_alt in my example.

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 code (note using Sys.setenv(PGHOST="myhost", PGDATABASE="mydb"), etc, elsewhere makes this reprex()-generated code run for anyone):

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"

John · Answer 2 · 2023-05-26T05:45:09.697

For clarity: RPostgreSQL and RPostgres are different libraries, despite the very similar names.

In my experience, in addition to the time zone errors listed in Ian Gow's answer, RPostgreSQL may drop times from dates/times when reading "TIMESTAMP WTIHOUT TIME ZONE" data from a PostgreSQL database into R. I opened a ticket for that error years ago and it seemed to be fixed for a while, but is either broken again, or else the error is intermittent.

I recently switched to the Rpostgres library. It was relatively painless because the interfaces of the two libraries are nearly identical, and so far it has been handling times correctly.

Timezone and POSIXct handling in RpostgreSQL

2 Answers2