How to get date using sqldf?

Question

I have a data frame that has a "DATE" field. e.g.: "24-10-2015"

The variable is in the date format.

When I use sqldf, e.g.: select min(DATE), MAX (DATE) from table ... the output is a number like 16623.

Tried FORMAT and CONVERT but they don't work in sqldf.

Any hints?

Answer 1

Specify the methods for each column in the data frame. Assume 'data' is the name of the data frame with the column name 'd' containing the 'Date' format.

Try the following:

sqldf('select max(d) as MAX__Date,
              min(d) as MIN__DATE
       from data',
      method = "name__class")

This should work.

Answer 2

I suggest you transform your date to POSIXct using as.POSIXct so that you can work with the date function of SQLite:

Using some random data:

#notice I keep the class of the date as POSIXct
#it really does not change anything
df <- data.frame(date = as.POSIXct('2015-01-01'))

#> df
#        date
#1 2015-01-01

And then you can do:

#using the date function in SQLite you convert the nanoseconds
#produced by min(date) back to a date.
sqldf('select date(min(date), "unixepoch", "localtime") from df')
  date(min(date), "unixepoch", "localtime")
1                                2015-01-01

And you have what you need. There is more info about how SQLite understands dates here

Answer 3

An other possibility is to not change your sqldf function and then to convert your dates stored as numbers. You can use as.Date() for this :

zoo::as.Date(16623)
[1] "2015-07-07"

As LyzandeR mentioned, you should specify an origin which states what the first date is. If you are using the zoo package, the default is "1970-01-01" and for your format it is probably the correct origin, but if you don't use it (meaning you sue the function from the base package then you must specify it.

as.Date(16623, origin = "1970-01-01")
[1] "2015-07-07"

But if you had dates from Excel you should change the origin :

zoo::as.Date(42313)
[1] "2085-11-06"

as.Date(42313, origin = "1899-12-30") # for Windows, use "1904-01-01" for Mac
[1] "2015-11-05" # correct result

I actually found why not supplying origin was working for me : I had the package zoo loaded, in which "1970-01-01" is the default option for origin:

base::as.Date(16623)
Error in as.Date.numeric(16623) : 'origin' must be supplied

zoo::as.Date(16623)
[1] "2015-07-07"

Here are the codes where you can see that zoo specifies a default origin for the function as.Date.numeric which is not the case for the base package :

base::as.Date.numeric
function (x, origin, ...) 
{
    if (missing(origin)) 
        stop("'origin' must be supplied")
    as.Date(origin, ...) + x
}
<bytecode: 0x17190e78>
<environment: namespace:base>

zoo::as.Date.numeric
function (x, origin, ...) 
{
    if (missing(origin)) 
        origin <- "1970-01-01"
    if (identical(origin, "0000-00-00")) 
        origin <- as.Date("0000-01-01", ...) - 1
    as.Date(origin, ...) + x
}
<environment: namespace:zoo>