Message-ID: <CAP01uRn5npGcVXDPRoEkWkpiR0_Cv7NpCgbh6N7jJRe3zwfn5g@mail.gmail.com>
Date: 2014-08-14T20:35:20Z
From: Gabor Grothendieck
Subject: Sldf command returns negative value for date
In-Reply-To: <CAOsJHwDButp9jQyZJdJu9+kXo8Na3EeCXzAkoFtHTKTSZCUK5Q@mail.gmail.com>
On Thu, Aug 14, 2014 at 3:47 PM, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
> Hi All!
>
> I am trying to increment date column of data frame so as to merge it with
> another data frame using sqldf:
> my query is :
> merge<-sqldf("select m.* ,e.* from mdata as m left join event as e on
> date(m.Datest,'+1 day')=e.Start")
>
> The query returns null for all columns related to event table.
> When I investigated further with query :
> sqldf("select date(Datest,'+1 day')") from eventflight;")
> gives me -ve valued dates like : -4671-02-15
>
> However this works:
> sqldf("select date(('2009-05-01'),'+1')")
>
> Dataframes are as follows:
> mdata :
> LOS Arrivals BookRange Datest
> 1 1283 0-4 2009-05-01
> 1 1650 0-4 2009-05-08
> 1 1302 5-9 2009-05-15
>
> event:
> Event.Name Event.location Start End
> Birthday Texas (US) 2009-05-02 2009-05-03
> Anni Texas (US) 2009-05-09 2009-01-11
>
> What am I doing wrong?
This is a FAQ. See #4 here: http://sqldf.googlecode.com .
The SQLite date function assumes its argument is a timestring but R
"Date" class variables are transferred to SQLite as days since
1970-01-01 so just add 1.
sqldf("select * from mdata as m left join event on Datest+1 = Start")
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com