Search code examples
rsqlitersqlite

Writing date and time into SQL database in R

I am trying to create a SQL database using a data set with a column that has both date and time included in it. The problem that I am running into is when the data is written into a SQL database, and read back into R the date and time column end up having a numeric structure rather than a Posixct structure or does not show the date and times correctly.

I have been using the RSQlite and DBI package to work between the two. I just started working with SQL, is there an appropriate way in reading date and time columns into a SQL database?

Thank you for your time.

Solution

  • SQLite does not support date and time types. Here are some options:

    1. convert the date/time fields back to R classes yourself. You could write a separate function for each table read into R that reads in the table and does the conversion transparently or you could adopt a naming convention for the columns that lets a single function perform the conversion according to the naming rules if you control the database itself. Another way to implement a naming convention, other than writing your own function, is to use the sqldf package. If you use sqldf("select ...", dbname = "mydb", connection = con, method = "name__class") it will convert every column whose name has two underscores to the class name after the two underscores. Note that name__class has two underscores as well.
    2. the dbmisc package (also see this) can perform conversions. You must prepare a schema, i.e. layout specification, for each table, as described there, in order to use it.
    3. Use a different database that does support date/time types. I usually use the H2 database in such cases. The RH2 package includes the entire H2 database software right in the R driver package in a similar manner to RSQLite.
    4. As per a comment below, the latest version of RSQLite has support for time and date fields; however, note that that that is on the R side, like the other solutions above (except using H2), and does not change the fact that SQLite itself has no such support so, for example, if you use SQL to modify such a field such as adding 1 to get the next date it will no longer be of the same type.