Hi.
On Fri 2002-07-26 at 08:34:51 -0400, [EMAIL PROTECTED] wrote:
[...]
> set the dates up as DATETIME fields. However, indexing on these forces me to
> specify a date and time or a range. i.e.
>
> where ReceivedDate = '2002-07-26 08:15:05'
>
> or
>
> where ReceivedDate >= '2002-07-26' and ReceivedDate < '2002-07-27'
>
> Since most of the queries I would run that would take the date into
> consideration would be to find every document that was received on a given
> date am I better off making two fields, one for ReceivedDate and one for
> ReceivedTime or is there a way to create an index only on the Date portion
> of a DATETIME field?
Your perception of the concept index seems a bit bend. Even if you
could specify such an index, you would still have the same problem, as
in queries you can access columns, not indexes. Indexes are only used
by the query optimizer to retrieve the rows in question faster.
Ideally, this is a transpararent mechanism.
In your example, the index on ReceivedDate will also be used to
retrieve the result for
WHERE ReceivedDate >= '2002-07-26' AND ReceivedDate < '2002-07-27'
To accomplish what you want, you could write the Date with LIKE, e.g.
WHERE ReceivedDate LIKE '2002-07-26 %'
but this would quite surely prevent the use of any index on this
column, as would any try to extract the date part with a function
(this would be an expression and expressions cannot use indexes in
MySQL).
You could, as you suggested, split this into two fields. And if your
common usage does not use the time part, this looks like a reasonable
solution. I, personally, need the time part more often and "simply"
write my queries this way:
WHERE Received BETWEEN '$date 00:00:00' AND '$date 23:59:59'
where $date is a variable from my application. Not really pretty, but
works fine.
In short: There probably is not The One, Right Solution.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
