0

I'm trying to exploit the wild fires data set from kaggle (https://www.kaggle.com/rtatman/188-million-us-wildfires). Specifically I want to use the "DISCOVERY_DATE" variable, and other date variables but I cannot manage to keep them as date format when I import them into R.

I'm using DBI and RSQLite to read the data.

library(DBI)
library(RSQLite)
library(dplyr)
rep <- "data/"
con <- dbConnect(drv=RSQLite::SQLite(), 
                 dbname=paste0(rep,"FPA_FOD_20170508.sqlite"))

fires <- dbReadTable(con, "Fires")
head(select(fires,DISCOVERY_DATE))

  DISCOVERY_DATE
1        2453404
2        2453138
3        2453157
4        2453185
5        2453185
6        2453187

I don't know how to convert this into a date format either. I have tried as.Date(x) but it looks like I would have to guess the origin. I don't see any arguments allowing one to specify columns types in the dbReadTable function either. Is there a method, preferably using these libraries, to handle date formats upong reading the data?

Now amidst the many related questions, the following seems to correspond to my problem but it has no answer. dbReadTable coercing date column from SQL database to character

Thanks in advance.

flafont11
  • 137
  • 7
  • Have you tried datetime(DISCOVERY_DATE)? – norie Apr 16 '21 at 10:17
  • Where is the datetime function from? cannot seem to find it. – flafont11 Apr 16 '21 at 11:43
  • SQLite does not support date or date/time types -- it typically represents them as plain numbers. If you have an example where you have both the stored value in the database and what date it represents then you can deduce the units and origin. Otherwise, you can try both seconds and days and for each try common origins for Date and POSIXct classes. These are given in my article in R News 4/1. https://www.r-project.org/doc/Rnews/Rnews_2004-1.pdf – G. Grothendieck Apr 16 '21 at 12:18
  • Thanks. I don't think I have access to this. Does this mean that once the data enters an SQLite format, it changes to plain numbers and I have no way to visualize it as a date format? – flafont11 Apr 16 '21 at 12:43
  • Yes, that information is lost; however, the heuristic in my last comment might allow you to guess it. – G. Grothendieck Apr 16 '21 at 12:52
  • Yes thank you. There was indeed a variable with the year, and one with the day in the data set. With the day it straight forward to see that it was simply the number of days since the first of january. So I ended up doing ```date <- as.Date(DAY,origin=paste0(YEAR,"-01-01"))```. Thank you for the help. – flafont11 Apr 16 '21 at 12:57
  • In SQLite [date in integer storage](https://www.sqlite.org/datatype3.html#:~:text=SQLite%20does%20not%20have%20a%20storage%20class%20set%20aside%20for,SSS%22). format is unix time: a number of seconds since 1970-01-01. So origin is known – astentx Apr 16 '21 at 14:28

0 Answers0