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

[Tomas Lanczos]

Being more specific:

On Fri, 2009-09-11 at 17:03 +0200, Tomas Lanczos wrote:
> 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. 

the resulting "date" field is imported into the dataset as character:

> date
  [1] "03-04-1991" "07-04-1972" "10-03-1997" "02-12-1991" "04-11-1997"
  [6] "11-03-1996" "04-11-1975" "08-31-1992" "04-03-1991" "10-10-1994"
 [11] "05-10-1992" "03-28-1972" "11-30-1992" "03-20-1981" "08-26-1991"
 [16] "03-08-1992" "02-10-1991" "09-05-1995" "02-12-1975" "03-12-1990"
 [21] "06-04-1992" "01-14-1991" "09-12-1971" "06-11-1991" "02-06-1975"
 [26] "02-09-1981" "11-21-1980" "02-09-1991" "08-09-1997" "02-10-1990"
 [31] "09-09-1996" "11-19-1996" "06-08-1990" "08-11-1995" "01-25-1994"
 [36] "02-06-1992" "11-22-1994" "07-17-1995" "12-09-1994" "09-05-1994"
 [41] "03-09-1990" "04-22-1975" "03-13-1995" "11-08-1997" "01-07-1991"
 [46] "07-14-1997" "11-09-1995" "01-17-1995" "09-10-1974" "08-19-1995"
 [51] "02-14-1995" "05-27-1973" "06-21-1994" "07-15-1996" "09-23-1975"
 [56] "02-12-1997" "06-05-1991" "04-03-1992" "04-02-1975" "05-11-1990"
 [61] "12-21-1980" "05-10-1973" "08-29-1980" "03-06-1991" "03-02-1992"
 [66] "04-05-1992" "10-18-1995" "06-02-1991" "11-08-1975" "10-21-1996"
 [71] "09-17-1973" "06-01-1992" "06-07-1992" "12-04-1994" "12-08-1996"
 [76] "07-20-1981" "07-23-1973" "10-20-1980" "08-15-1994" "05-06-1995"
 [81] "06-17-1996" "02-06-1997" "06-10-1997" "03-31-1972" "04-14-1997"
 [86] "02-23-1981" "06-23-1975" "02-17-1994" "10-04-1995" "05-12-1995"
 [91] "05-15-1981" "10-16-1981" "09-04-1996" "03-12-1996" "10-02-1997"
 [96] "11-24-1981" "01-16-1996" "05-21-1996" "07-18-1994" "03-22-1994"
[101] "05-13-1997"


> I appreciate every advice/suggestion/help.
> 
> Best regards
> 
> Tomas
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.