468,256 Members | 1,423 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,256 developers. It's quick & easy.

Comparing indexed MySQL datetime columns

Hi,

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?

Thanks, - Dave

Mar 8 '06 #1
3 3205
WHERE TABLE1.depart_day LIKE '2006-03-08%' AND TABLE2.depart_day LIKE
'2006-03-08%'

% = wildcard, but you must use LIKE instead of =

Mar 8 '06 #2
This is a mysql question and should be asked in a mysql groups.
Nonetheless:

try date_diff (if mysql > 4.1):
select foo from bar where date_diff(col1, col2) = 0

otherwise:
try using date_format or extract() then date_sub() to find the
difference

As for using the indices, I dunno.

Mar 8 '06 #3
On Wed, 08 Mar 2006 09:26:33 -0800, la***********@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

Mar 9 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Marc G. Fournier | last post: by
2 posts views Thread by Manny Chohan | last post: by
19 posts views Thread by Dennis | last post: by
7 posts views Thread by greywire | last post: by
2 posts views Thread by nephish | last post: by
Atli
6 posts views Thread by Atli | last post: by
6 posts views Thread by Geoff Cox | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.