Skip to content

Sldf command returns negative value for date

3 messages · Sneha Bishnoi, Gabor Grothendieck

Sneha Bishnoi
# 
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?

Thanks in advance
SB
Gabor Grothendieck
#
On Thu, Aug 14, 2014 at 3:47 PM, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
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")
Sneha Bishnoi
# 
It works :) Thanks so much! I tried searching a lot but I guess i missed
this fact!


On Thu, Aug 14, 2014 at 4:35 PM, Gabor Grothendieck <ggrothendieck at gmail.com