[SOLVED] Match datetime by the minute (not an exact match by the second) [mysql]

hattori.hanzo · 10-21-2010, 03:13 AM

I have the following query:

Code:

$sql="SELECT table1.datetime, table1.user_id, table2.ip, FROM table1,table2 WHERE id='$id' AND (table1.id = table2.id AND table1.datetime = table2.datetime)";

In table2 the datetime fields are about 1 to 2 seconds off due to the source of the data, which I cannot change.

Is it possible via a query match table1.datetime & table2.datetime by HH:MM (ie. to the minute instead of to the second)?

Thanks & regards

hattori.hanzo · 10-21-2010, 05:43 PM

Quote:

Originally Posted by hattori.hanzo

I have the following query:

Code:

$sql="SELECT table1.datetime, table1.user_id, table2.ip, FROM table1,table2 WHERE id='$id' AND (table1.id = table2.id AND table1.datetime = table2.datetime)";

In table2 the datetime fields are about 1 to 2 seconds off due to the source of the data, which I cannot change.

Is it possible via a query match table1.datetime & table2.datetime by HH:MM (ie. to the minute instead of to the second)?

I used:

Code:

DATE_FORMAT( table1.datetime, '%Y-%m-%d %H:%i' ) =  DATE_FORMAT( table2.datetime, '%Y-%m-%d %H:%i' ))

which solved it.