0

I have a SQLite table containing a DATETIME column expressed in milliseconds since epoch. That is a 14 digits integer. I import the table into R with the following

  require(DBI)
  con <- dbConnect("SQLite", dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1, encoding="utf-8")
  dbClearResult(query)
  dbDisconnect(con)
  return(result)

yet my column is imported as 32-bit integer column (up to 10 digits) with nefarious consequences.

How should I specify the target file type for the column?

Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
CptNemo
  • 6,455
  • 16
  • 58
  • 107

1 Answers1

0

You should try to cast it to a floating point number (after possibly dividing by 1e3), which R will read as a numeric.

You can then transform the numeric value into a standard Datetime in R via as.POSIXct().

In case you can consider alternatives, the RPostgreSQL interface to PostgreSQL handles the SQL datetime to R Datetime transparently; the RMySQL one does not.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725