[R] format (?) problems with data imported from postgres

Tomas Lanczos lanczos at fns.uniba.sk
Fri Sep 11 17:03:15 CEST 2009


Good day,

I read some data from a PostgreSQL database by a following script:

library(Rdbi)
library(RdbiPgSQL)
# conn becomes an object which contains the DB connection:
conn <- dbConnect(PgSQL(), host="localhost", dbname="BVS",
user="postgres", password = "*******")

query_duj_kal <- dbSendQuery(conn, "select zdroj as well, cas as date,
fe2, fe3, mn, nh4, no2, no3, o2, teplota as temperature from analystats
where zdroj like 'Dunaj Kalinkovo'")
watchem_duj_kal <- dbGetResult(query_duj_kal)

My intention with the data is to create a time series by a following
script:

date <- (watchem_duj_kal$date)
NO3 <- (watchem_duj_kal$no3)
NH4 <- (watchem_duj_kal$nh4)
maxy<-max(NO3,NH4)                   
miny<-min(NO3[NO3>0],NH4[NH4>0])     
date_p <- as.POSIXct(date, "CET")
par(mfrow=c(2,1), ylog = TRUE, yaxp = c(0.01, 100, 3))
plot(date_p, NO3,log = "y", type = "l", col = "darkred",
 main = "NVZ-1", xlab = "time", ylab = "NO3-" )
lines(date_p, NH4, col = "darkblue", lty = "dotted")
plot(date_p, NH4, log = "y", type = "l", col = "darkblue", main =
"NVZ-1",
xlab = "time", ylab = "NH4+" )

The first problems comes with the definition maxy and miny (following a
previous advice od Christian Poersching through this mailing list) what
worked pretty well with data imported from a csv file by a read.table()
function, but in this case I got NA output 

The next problem comes with the unambiguous format od the "date" field,
which is in the database defined as YYYY-MM-DD, but in the resulting
"watchem_duj_kal" dataset is strangely converted to DD-MM-YYYY, what is
unambiguous for the as.POSIXct() function expecting YYYY-MM-DD. A
function converting the format of the date should help, but I could not
find untill now. 

I appreciate every advice/suggestion/help.

Best regards

Tomas




More information about the R-help mailing list