[R] Time and db precision

[Marc Schwartz]

On May 26, 2011, at 10:04 AM, Mikkel Grum wrote:

> Thanks Marc,
> 
> I had just come up with another, slightly more convoluted solution. Add as.is = TRUE to the query and then get the timetoken with
>  timetoken <- df$timestamp[df$timestamp == max(as.POSIX(df$timestamp))]
> 
> While it looks like options(digits.secs = 6) works, I worry that theoretically it just pushes the problem down to another level of decimal points. With the solution above, I apparently get the exact same value that was in the database.
> 
> Interestingly, 
>  timetoken <- max(as.POSIX(df$timestamp))
> does not appear to give me the same result.
> 
> Demo:
>> a <- "2011-05-25 22:15:11.027116000"
>> b <- "2011-05-25 22:15:11.027117000"
>> c <- "2011-05-25 22:15:11.027118000"
>> d <- c(a, b, c)
>> d
> [1] "2011-05-25 22:15:11.027116000" "2011-05-25 22:15:11.027117000" "2011-05-25 22:15:11.027118000"
>> d[d == max(as.POSIXct(d))]
> [1] "2011-05-25 22:15:11.027118000"
>> max(as.POSIXct(d))
> [1] "2011-05-25 22:15:11 COT"


Hi Mikkel,

>From what I can tell, PostgreSQL stores timestamp data with millisecond precision:

  http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL

So 6 decimal place precision in R should be more than sufficient.


You have a similar problem in your last example here:

# This is returning the indexed value in the original character vector 'd'
# not the coerced POSIXct object. So you get the original quoted string, 
# including the irrelevant trailing 0's

> d[d == max(as.POSIXct(d))]
[1] "2011-05-25 22:15:11.027118000"


# Now you are printing POSIXct objects, which have the same limitation as I raised earlier:

> max(as.POSIXct(d))
[1] "2011-05-25 22:15:11 CDT"

options(digits.secs = 6)

> max(as.POSIXct(d))
[1] "2011-05-25 22:15:11.027118 CDT"


This is the classic problem of differentiating between how R is storing the data internally and how R *prints* the data to the console via various default formatting options.

Regards,

Marc