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