3

I am confronted with a weird phenomenom when extracting data out of a SQLite 3 database using the RSQLite 1.0.0 package. All except the year of the datetime value gets truncated!

See an example:

Following I am extracting a DATETIME column:

library(RSQLite)
src_path <- "../DataLocked/Study.db"
con <- dbConnect(SQLite(), src_path)
dbGetQuery(con, "SELECT Todesdatum FROM Kontraindikation LIMIT 10")

This gets me the following result:

        Todesdatum
   1          NA
   2          NA
   3        2004
   4          NA
   5        2006
   6          NA
   7          NA
   8          NA
   9          NA
   10         NA

Now from the same table I am omitting NON NULL values:

dbGetQuery(con, "SELECT Todesdatum FROM Kontraindikation WHERE Todesdatum NOTNULL")

This gets me the following result:

            Todesdatum
1  2004-09-16 00:00:00
2  2006-04-20 00:00:00
3  2006-06-02 00:00:00
4  2007-09-15 00:00:00
5  2008-06-12 00:00:00
6  2005-10-04 00:00:00
7  2008-11-22 00:00:00
8  2005-12-22 00:00:00
9  2006-11-05 00:00:00
10 2006-02-08 00:00:00
...

Now I try to do a string format on the DATETIME field:

dbGetQuery(con, "SELECT strftime('%Y-%m-%d',Todesdatum) as fixed_Todesdatum FROM Kontraindikation 10")

That works:

        fixed_Todesdatum
   1          <NA>
   2          <NA>
   3       2004-09-16
   4          <NA>
   5       2006-04-20
   6          <NA>
   7          <NA>
   8          <NA>
   9          <NA>
   10         <NA>

I have really no idea what is going on and how to solve this. I would be very thankful for any pointers.

Greetings, Alex

krlmlr
  • 25,056
  • 14
  • 120
  • 217
Alexander Meyer
  • 31
  • 2
  • Unlike other RDMS's, there is no [datetime](http://www.tutorialspoint.com/sqlite/sqlite_data_types.htm) storage class in SQLite3 *but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values*. So is the column a numeric or string type? – Parfait Jul 08 '16 at 00:45
  • Thanks very much for your response. The data type is in all columns TEXT. – Alexander Meyer Jul 08 '16 at 08:13
  • What is the problem? What is your desired result? Wouldn't the last query work? – Parfait Jul 08 '16 at 17:06
  • The desired output is actually 'YY-mm-dd HH:mm:ss'. This is also the correct behavior. However, if NULL values exists in the table it seems to the RSQLite driver chops everythings except the year. I am pretty sure it is a RSQLite bug. I did quite alot if research before asking this question here - I just wanted to make sure I did not make a mistake. Or maybe somebody else has made this observation before. I will submit it now as an Issue on GitHub. Thanks very much for your efforts. – Alexander Meyer Jul 09 '16 at 06:50

1 Answers1

0

Current RSQLite is a bit weak when it comes to the detection of the data type. Unlike most DBMS, SQLite can store arbitrary data types in each entry of a column.

You didn't submit a reproducible example, but I was able to replicate the problem:

library(tibble)
library(DBI)

a <- tibble(a = c(NA_integer_), b = 1)
b <- tibble(a = c("2014-06-04 00:00:00"), b = 2)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "test", a)
dbWriteTable(con, "test", b, append = TRUE)

dbReadTable(con, "test")

The two data frames have different data types for the a column, by writing a first this column is created as INTEGER. If I change NA_integer_ to NA_character, the problem vanishes. Can you replicate this?

It could be that your Todesdatum column has INTEGER or REAL affinity, which tricks RSQLite into believing that the first value is a number. Update the column affinity, or use your strftime workaround with your desired formatting.

Anyway, I agree that RSQLite should do a better job here.

krlmlr
  • 25,056
  • 14
  • 120
  • 217
  • Thank you very much for your answer! I can replicate your code. I tried to change the type affinity. It does not work. Problem is the NULL value. typeof(NULL) is NULL and I cannot change the affinity for NULL values. I will work with the strftime workaround until the problem is solved. Anyway, thank you so much for your effort and the great package! – Alexander Meyer Jul 12 '16 at 07:46