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

[Dirk Eddelbuettel]

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. .

Dirk

-- 
Three out of two people have difficulties with fractions.