Issue 76 in rpostgresql: RPostgreSQL does not handle 'infinity' and '-infinity' special timevalues in PostgreSQL

[rpost...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 76 by kaminsky...@gmail.com: RPostgreSQL does not
handle 'infinity' and '-infinity' special timevalues in PostgreSQL
https://code.google.com/p/rpostgresql/issues/detail?id=76

When trying to select from a table that has 'infinity' special values in
SQL, RPostgreSQL cannot convert the values to dates. See the code below:

dateclass = "timestamp"
zz = "UTC"

stopifnot(require(RPostgreSQL))

## Force a timezone to make the tests comparable at different locations
Sys.setenv("PGDATESTYLE"="German")
Sys.setenv("TZ"="UTC")

## load the PostgresSQL driver
drv <- dbDriver("PostgreSQL")
## can't print result as it contains process id which changes
print(summary(drv))

## connect to the default db
con <- dbConnect(drv,
user=Sys.getenv("POSTGRES_USER"),
password=Sys.getenv("POSTGRES_PASSWD"),
host=Sys.getenv("POSTGRES_HOST"),
dbname=Sys.getenv("POSTGRES_DATABASE"),
port=ifelse((p<-Sys.getenv("POSTGRES_PORT"))!="", p,
5432))

dbGetQuery(con, "SET TIMEZONE TO 'UTC'")


if (dbExistsTable(con, "tempostgrestable"))
dbRemoveTable(con, "tempostgrestable")

dbGetQuery(con, paste("create table tempostgrestable (tt ",
dateclass, ", zz integer);", sep=""))
dbGetQuery(con, paste("insert into tempostgrestable
values('", "infinity", "', 1);", sep=""))
dbGetQuery(con, paste("insert into tempostgrestable
values('", "-infinity", "', 2);", sep=""))
res <- dbReadTable(con, "tempostgrestable")
print(res)

res <- dbSendQuery(con, "select tt from tempostgrestable;")

Returns this error:

Error in as.POSIXlt.character(x, tz, ...) :
character string is not in a standard unambiguous format

I think that R should return infinite date values instead of an error. That
can be achieved with as.POSIXct(Inf, origin="1970-01-01")

I've implemented a fix on a branch of the project on github. You can see
the commit here:
https://github.com/mikekaminsky/rpostgresql/commit/83335e6d0d0ac8c080a87040ffe4030e7447189f

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings