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?

Constitute

SO creates long-term usable information for current & future readers. Questions should be self-contained with the minimal code & data needed so readers can copy it from the question & paste it into a running R session & see what the poster sees. The data should be part of the question & not elsewhere to ensure that it continues to be vailable. Use the R command dput(X) to provide X in reproducible form. See (1) #5963769 (2) stackoverflow.com/help/mcve (3) stackoverflow.com/help/how-to-ask – Abrahamabrahams

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.

Spatz

This was my preferable method, since no changes are made on the data of the data frame. And IT WORKS! – Constitute

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

Mellette

An other solution is to use as.Date(16623) which will return "2015-07-07" – Blither

Thanks @Blither . How do you mean? – Mellette

When saxo runs his code the output are numbers so after his code he can convert the numbers into dates using as.Date(number) which will return a Date. – Blither

@Blither I see. So, you mean after running the sqldf function. This is a possibility for sure. You still need to provide the origin argument in order to work but you are right. You may want to write it as an answer. Thanks :) – Mellette

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>

Blither

Good one! On my computer as.Date(16623) returns an error: 'origin' must be supplied . This is probably a < R 3.2 error. – Mellette

@LyzandeR: I don't know why it works on my computer as I have R 3.0.2 But in the help it is written "as.Date will accept numeric data (the number of days since an epoch), but only if origin is supplied." so the best practice is to include the origin – Blither

Answer 4

If you only need to check specific date values and not date ranges, you could convert your date column into a string using as.character before you call your sql code, like so:

table$date2 = as.character(table$date)
sqldf("select max(date2), min(date2) from table")

This worked really well for me and I haven't run into any drawbacks yet.

from: https://ladvien.com/sqldf-dates/

Michaeu