6

I'm using the RPostgreSQL package to load data from a PostgreSQL data base.

The problem is that a datetime column (POSIXct) is automatically convert into a date.

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="abc",host="def ",port=1234,user="ghi",password="jkl" )

Instead of using this:

df = dbGetQuery(con, "
    SELECT customer_id, dttm_utc
    FROM  schema.table;")

I have to use that:

df = dbGetQuery(con, "
     SELECT customer_id, to_char(dttm_utc, 'MM-DD-YYYY HH24:MI:SS') as dttm_utc,
     FROM  schema.table;")

If I don't I loose the time and only recover dates.

I noticed this probem doesn't occur if I only want the first 1000 rows. It appears almost all the time when there is more than 300 000 rows.

How can I fix this ?

  • 1
    any updates on this? I think i'm running into a similar issue – zebrainatree May 11 '15 at 17:03
  • I did the test and it doesn't happens to me. In my case, ddtm_utc is of type `timestamp(6) with time zone` in the database... object in R is `str(df) 'data.frame': 7 obs. of 2 variables: $ customer_id: int 0 1 22 21 24 27 28 $ dttm_utc : POSIXct, format: "2020-03-02 21:00:00" "2021-12-29 21:00:00" ...` – Miguel Suazo Aug 27 '21 at 16:48
  • did it with 300K rows also `str(df) 'data.frame': 300000 obs. of 2 variables: $ customer_id: num 7 8 9 10 11 12 13 14 16 68 ... $ dttm_utc : POSIXct, format: "2020-05-31 20:00:04" "2020-05-31 20:00:05" ... > ` – Miguel Suazo Aug 27 '21 at 18:03

0 Answers0