How to get date using sqldf?

Question

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

The variable is in a date format.

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

Tried FORMAT and CONVERT, but they do not work in sqldf.

Any clues?

Answer 1

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

Try the following:

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

That should work.

Answer 2

POSIXct as.POSIXct, date SQLite:

:

#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

:

#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

, . , SQLite

Answer 3

- sqldf, , . as.Date() :

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

LyzandeR, origin, , . zoo, "1970-01-01", , , , ( , base , .

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

Excel, :

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

, origin : zoo, "1970-01-01" - origin:

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

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

, , zoo origin as.Date.numeric, base:

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>

Answer 4

, , , as.character, SQL-, :

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

, - .

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