[R] Fwd: stfrtime function not returning proper results through sqldf package in R

[Gabor Grothendieck]

1. Convert the date from R's origin to the origin used by SQLite's
strftime function and then be sure you are using the correct SQLite
strftime syntax:

    library(sqldf)
    sqldf("select strftime('%m', Date + 2440588.5) month from log")

2. Alternately use the H2 backend which actually supports dates unlike
SQLite which only supports functions that interpret certain numbers
and character strings as dates.

    library(RH2) # if RH2 is loaded sqldf will default to the H2 database
    library(sqldf)
    sqldf("select month(Date) from log")

Note that the first time you use sqldf with RH2 in a session it will
load java which is time consuming but after that it should run ok.

Note: See

1. the sqldf home page which has more info on dates and times:
https://github.com/ggrothendieck/sqldf

2. the sqldf help page:
?sqldf

3. the SQLite date and time function page which explains SQLite's
strftime function
https://www.sqlite.org/lang_datefunc.html

4. the H2 documentation:
http://www.h2database.com

On Fri, Sep 16, 2016 at 12:35 AM, Manohar Reddy <manu.reddy52 at gmail.com> wrote:
> Hi ,
>
>
>
>   I have data something looks like below (or PFA), but when I’m extracting
> month  using *strftime*  function through *sqldf* library ,it’s returning
> below results but it’s not returning exact results ,it supposed to return
>  05,05,05,06,06,06.Can anyone please guide me how to do that with *strftime*
> function.
>
>
>
> Thanks in advance.
>
>
>
> Quiries :
>
>
> library(scales)
>
> # load data:
> log <- data.frame(Date =
> c("2013/05/25","2013/05/28","2013/05/31","2013/06/01","2013/06/02","2013/06/05","2013/06/07"),
>   Quantity = c(9,1,15,4,5,17,18))
>
>
> # convert date variable from factor to date format:
> log$Date <- as.Date(log$Date,
>   "%Y/%m/%d") # tabulate all the options here
> str(log)
>
>
>
>
>
>
>
> Manu.
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com