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
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
