[R] Merge two data frames on date field

[arun]

Hi,You haven't provided any example dataset. 

 set.seed(42)
 dat1 <- data.frame(dates=seq(as.POSIXct("2009-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S"),by= '12 hour', length=12),Field1=rnorm(12),Field2=LETTERS[1:12])

set.seed(395)
 dat2 <- data.frame(dates=seq(as.POSIXct("2009-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S"),by= '6 hour', length=12),Field1=rnorm(12),Field2=sample(LETTERS,12,replace=FALSE))


merge(dat1,dat2,by="dates")
#                dates   Field1.x Field2.x   Field1.y Field2.y
#1 2009-01-01 00:00:00  1.3709584        A -1.2152900        V
#2 2009-01-01 12:00:00 -0.5646982        B -1.2771657        P
#3 2009-01-02 00:00:00  0.3631284        C  0.4829300        W
#4 2009-01-02 12:00:00  0.6328626        D  0.5468625        Y
#5 2009-01-03 00:00:00  0.4042683        E -1.7113256        I
#6 2009-01-03 12:00:00 -0.1061245        F  0.4851851        B


merge(dat1,dat2,by="dates",all=TRUE)[1:5,]
                dates   Field1.x Field2.x   Field1.y Field2.y
1 2009-01-01 00:00:00  1.3709584        A -1.2152900        V
2 2009-01-01 06:00:00         NA     <NA> -0.4633211        G 
3 2009-01-01 12:00:00 -0.5646982        B -1.2771657        P
4 2009-01-01 18:00:00         NA     <NA>  0.6011199        N
5 2009-01-02 00:00:00  0.3631284        C  0.4829300        W
A.K.


why wont two tables merge correctly on a date? 

I have one table with a field called 'date' "2009-01-01 00:00:00". 
The other table also has a field call 'date'. 

The dates are something like  "2009-01-01 01:00:00". 

The two tables are not merging correctly. The dates are not lining up. <NA>

The table also has other fields with dates in them. 

Sorry the data set is very big so I have not loaded it. 

The code I'm using to merge is m2 <- merge(m1 ,mt,all=TRUE)