search

tomoakin / RPostgreSQL

Automatically exported from code.google.com/p/rpostgresql
64 stars 20 forks source link

Added workaround to infinity dates #89

Closed Prev-I closed 6 years ago

Prev-I commented 7 years ago

I've cleaned up and implemented the workaround found here #75 to make sure RPostgreSQL handle the value "infinity" for date types (Timestamp without TZ, Timestamp with TZ, Date)

tomoakin commented 7 years ago

Infinite date is not really understandable, and R do not seem to have such special date.

> as.POSIXct(Inf, origin="1970-01-01")
[1] NA
> as.POSIXct(-Inf, origin="1970-01-01")
[1] NA

Why at all have the database entered with infinity?

Prev-I commented 7 years ago

From official postgresql docs regarding date-time https://www.postgresql.org/docs/current/static/datatype-datetime.html

8.5.1.4. Special Values infinity | date, timestamp | later than all other time stamps -infinity | date, timestamp | earlier than all other time stamps

In some of ours collaborators databases those values are present and we had to make it work in R. The fact that those are valid postgresql values pushed me to think that an upstream fix is a correct solution for that problem. Off course I agree with you that a well designed DB should probably not use those values...

tomoakin commented 7 years ago

Do you think it is ok to treat +infinity, -infinity, and NULL the same way? It does not appear that R can handle the special value properly in a way such that +infinity is later than any normal date etc, or print the special value. As a adapter, RPostgreSQL have limitation due to the R functionality. If your database have +infinity, perhaps it is expected to be compared or sorted. Such operation would not be possible after import to R. So, the recipe would be compute at SQL level and import as text if necessary.

That said, perhaps we can treat +infinity and -infinity same as NULL, with warning rather than error.

Prev-I commented 7 years ago

Maybe the best way to handle this is to return NA and emit a warning. If using data in array with NA the length remains unchanged, unlike NULL which is removed.

tomoakin commented 7 years ago

What I meant is all infinity, -infinity, and NULL in the database is converted to NA in R. While conversion of NULL in database to NA is obvious, conversion of infinity and -infinity to NA have information loss and thus emit warning.

Please see the commit 55e5e08