Posted by Andy Jeffries on 03/09/06 15:31

On Wed, 08 Mar 2006 09:26:33 -0800, laredotornado@zipmail.com wrote:
> I have two columns, both MySQL 4 DATETIME types ...
>
> TABLE1.depart_day
> TABLE2.depart_day
>
> and both are indexed. The problem is, all of TABLE1's dates have a time
> of midnight (e.g. 2006-03-08 00:00:00" while TABLE2's columns have times
> (e.g. 2006-03-08 12:45:00). I want to match rows where TABLE1 and
> TABLE2's depart day columns match in terms of year, month, and day (don't
> care about time). How can I do this and also make use of any available
> indexes?

I think you'll have difficulty with indexes as you can't use them if
you're taking any action on a field. What you may be better doing is
creating a DATE column (rather than a DATETIME) column for each of them,
copying just the date in there and indexing/searching those columns (but
maybe displaying the DATETIME columns if you need to).

For your SQL query:

SELECT Foo FROM TABLE1, TABLE2 WHERE DATE(TABLE1.depart_day) =
DATE(TABLE2.depart_day);

But that won't use indexes....

Cheers,


Andy

--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

[Back to original message]