R help - Nov 2009 - RSQLite does not read very large values correctly

Hello,

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

It appears that you were reading the number in as an integer and not
numeric.  The value that you are seeing (-596864072) is the numeric
value trucated to 32 bit.  The number would have been in hex
(6DC6C93B8) but dropping the leading '6' you will get the result as a
32 bit integer.  Check your data base definition and how you are
reading in your data.

On Mon, Nov 30, 2009 at 11:14 AM, Ruecker, Sebastian
<Sebastian.Ruecker at commerzbank.com> wrote:
> Hello,
>
> 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-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.
>


-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?