0

I have a problem with importing data from edb postgres into R (with RPostgres). I using this code:

library(RPostgres)
conn <- dbConnect(Postgres(), dbname = 'xx', host = 'xx', port = 'xx', 
                  user = 'xx', password = 'xx',
                  options="-c search_path=xx")

a=dbGetQuery(conn, "SELECT * FROM xx")

After importing data into R is all Ok exept all column with date which become corruped. A few exemples:

postgres value: 2009-11-02  after importing in R: 0231-08-11
postgres value: 2009-08-12  after importing in R: 1206-01-15

data type in postgres for this column is date. If I read from postgres column (date) with query:

".. to_char(column_with_date, 'DD-MM-YYYY') as column_with_date .."

then I get ok data in R but data type in R is not date but character.

I tried the same with importing the same data from mysql base into R (with RMySQL) and in this case data are noncorrupted . Used code:

library(RMySQL)
conn <-  dbConnect(MySQL(), user = "xx", password = "xx", dbname = "xx", host = "xx" )
a=dbGetQuery(conn,"select* from xx ")

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user8795501
  • 73
  • 10
  • I have to believe this is a driver(RPostgres) issue. Probably raise this here:https://github.com/r-dbi/RPostgres/issues – Adrian Klaver Aug 07 '20 at 15:09
  • 1
    Can you show us your schema? For standard Date and Datetime types all relevant R packages --- including the RPostgreSQL package I co-wrote/mentored/maintained -- should just work. – Dirk Eddelbuettel Aug 09 '20 at 16:39
  • Hi, sorry but I do not know exactly about which schema you talk about. I checked little bit in postgresql.conf and found this: ``` #edb_redwood_date = on # translate DATE to TIMESTAMP(0) edb_redwood_date = off # translate TIMESTAMP(0) to DATE ``` Is maybe timestamp a reasons why R cannot read read date correct? – user8795501 Aug 10 '20 at 07:11

0 Answers0