Tomas Lanczos
2009-Sep-11 15:03 UTC
[R] format (?) problems with data imported from postgres
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
Tomas Lanczos
2009-Sep-11 15:46 UTC
[R] format (?) problems with data imported from postgres
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.