RPostgreSQL does not handle 'infinity' and '-infinity' special timevalues in PostgreSQL

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/83335e6d0d0ac8c080a87040ffe40
30e7447189f

Original issue reported on code.google.com by kaminsky...@gmail.com on 8 May 2015 at 9:07

sicarul / rpostgresql

RPostgreSQL does not handle 'infinity' and '-infinity' special timevalues in PostgreSQL #76