[R] Time and db precision

[Marc Schwartz]

On May 25, 2011, at 6:25 PM, Mikkel Grum wrote:

> I have a loop that regularly checks for new data to analyse in my database. In order to facilitate this, the database table has a timestamp column with the time that the data was inserted into the database. Something like this:
> 
> while (....) {
>    load(timetoken.Rdata)
>    df <- sqlQuery(con, paste("SELECT * FROM tabledf WHERE timestamp > ", timetoken, sep = ""))
>    analyse(df)
>    timetoken <- max(df$timestamp)
>    save(timetoken, file = "timetoken.Rdata")
>    Sys.sleep(60)
> }
> 
> Now this used to work fairly well with R and PostgreSQL on Windows, but on Ubuntu, I'm getting a lot of data being pulled up again and again. I suspect what is happening is that R is rounding off to the nearest second, while PostgreSQL is using a much higher level of precision, so that if no new data has come in in the meantime, chances are fairly high (50% ??) that the PostgreSQL timestamp is higher than the version that has been rounded off by R.
> 
> Is there any way of recording the timestamp in R exactly as it is in PostgreSQL? Or is there another way of dealing with this??
> 
> sessionInfo()
> R version 2.11.1 (2010-05-31)
> x86_64-pc-linux-gnu
> 
> locale:
> [1] C
> 
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
> 
> other attached packages:
> [1] RODBC_1.3-1
> 
> All assistance greatly appreciated.
> 
> Mikkel


This query is better suited for R-SIG-DB:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

That being said:

See ?POSIXct

Check the actual output of paste("SELECT * FROM tabledf WHERE timestamp > ", timetoken, sep = "") to see what value 'timetoken' is actually taking as it is used in the query construct. As is noted in the above help file, be sure that options("digits.secs") is properly set, since the default will be to round printed output to the nearest second:

# A clean R session on OSX
> options("digits.secs")
$digits.secs
NULL

# return current date/time as POSIXct

> Sys.time()
[1] "2011-05-26 08:11:37 CDT"

options(digits.secs = 6)

> Sys.time()
[1] "2011-05-26 08:12:07.080329 CDT"


options(digits.secs = 0)

> paste("SELECT * FROM tabledf WHERE timestamp > ", Sys.time(), sep = "")
[1] "SELECT * FROM tabledf WHERE timestamp > 2011-05-26 08:15:02"
 
options(digits.secs = 6)

> paste("SELECT * FROM tabledf WHERE timestamp > ", Sys.time(), sep = "")
[1] "SELECT * FROM tabledf WHERE timestamp > 2011-05-26 08:15:12.005103"


HTH,

Marc Schwartz