[R] Time and db precision

From: Mikkel Grum <mi2kelgrum_at_yahoo.com>
Date: Wed, 25 May 2011 16:25:35 -0700 (PDT)


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



R-help_at_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. Received on Wed 25 May 2011 - 23:33:29 GMT

This quarter's messages: by month, or sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

All messages

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 26 May 2011 - 13:40:10 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive