[R-sig-DB] FW: RSQLite does not read very large values correctly

[Seth Falcon]

Hi,

On 11/30/09 9:04 AM, Dirk Eddelbuettel wrote:
>
> On 30 November 2009 at 17:28, Ruecker, Sebastian 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!
>
> Hm, can you cast them to floating point representation?  Or else, convert to
> proper time types before storing or maybe before retrieving (if SQLite let's
> you).
>
> If these are seconds from/to the epoch, then double is not a bad choice. R
> itself does the same for POSIXct values to give us fractional time up to
> millisecond resolution. .

I think the issue here is that SQLite's integer type allows for much 
larger integers than R's integer type can handle.

One quick solution is to convert to double on the way out.  In a small 
test db this seemed to work from RSQLite.   Use a query like  "SELECT 
1.0 * bigIntCol from someTable"

+ seth

-- 
Seth Falcon | @sfalcon | http://userprimary.net/users