[R-sig-DB] FW: RSQLite does not read very large values correctly
Gabor Grothendieck
ggrothend|eck @end|ng |rom gm@||@com
Mon Nov 30 18:00:36 CET 2009
I am able to create sqlite tables with large numbers and read them back,
e.g.
> Lines <- "OrderID Day TimeToclose
+ 1 2009-11-25 29467907000
+ 2 2009-11-25 29467907000
+ 3 2009-11-25 29467907000"
> DF <- read.table(textConnection(Lines), header = TRUE)
> library(sqldf)
>
> # sqldf creates an sqlite database with table DF
> # uploading DF to it and reads it back
>
> DF2 <- sqldf("select * from DF")
>
> all.equal(DF, DF2, check.attributes = FALSE)
[1] TRUE
> DF
OrderID Day TimeToclose
1 1 2009-11-25 29467907000
2 2 2009-11-25 29467907000
3 3 2009-11-25 29467907000
> DF2
OrderID Day__1 TimeToclose
1 1 2009-11-25 29467907000
2 2 2009-11-25 29467907000
3 3 2009-11-25 29467907000
> R.version.string # Windows Vista
[1] "R version 2.10.0 Patched (2009-11-21 r50532)"
> packageDescription("RSQLite")$Version
[1] "0.7-3"
> packageDescription("DBI")$Version
[1] "0.2-4"
> packageDescription("sqldf")$Version
[1] "0-1.7"
On Mon, Nov 30, 2009 at 11:28 AM, Ruecker, Sebastian <
Sebastian.Ruecker using commerzbank.com> wrote:
> Hello,
>
> I posted this in the regular r-help list, but it may be more appropriate
> here. Sorry about the double posting!
>
> I am trying to import data from an SQLite database to R.
> Unfortunately, I seem to get wrong data when I try to import very large
> numbers.
>
> For example:
> I look at the database via SQLiteStudio(v.1.1.3) and I see the following
> values:
>
> OrderID Day TimeToclose
> 1 2009-11-25 29467907000
> 2 2009-11-25 29467907000
> 3 2009-11-25 29467907000
>
>
> Now I run this R Code:
>
> > library("DBI")
> > library("RSQLite")
> >
> > # DB Connection
> > con <- dbConnect(dbDriver("SQLite"), "C:/Temp/TickDB01.db")
> > raw_Data <- dbGetQuery(con, "SELECT OrderID, Day, TimeToClose FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
> OrderID Day TimeToClose
> 1 1 2009-11-25 -596864072
> 2 2 2009-11-25 -596864072
> 3 3 2009-11-25 -596864072
>
>
> The values are totally wrong... Is it because RSQLite has a problem with
> big numbers?
> TimeToClose is microseconds till 17:00.
>
> When I make the numbers smaller, it works again:
>
> > raw_Data <- dbGetQuery(con, "SELECT TimeToClose/1000 as TTC FROM
> Tr_TickData WHERE OrderID in (1,2,3)")
> > raw_Data
> TTC
> 1 29467907
> 2 29467907
> 3 29467907
>
>
> I would appreciate any help with this problem!
>
> Thanks and regards,
>
> Sebastian
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list