MySQL Forums
Forum List  »  Optimizer & Parser

Re: BETWEEN a DATE and a hard place
Posted by: Rick James
Date: May 02, 2014 11:39AM

The use of the function UNIX_TIMESTAMP() was added in 5.5.1:
CREATE TABLE t (c TIMESTAMP) 
PARTITION BY RANGE ( UNIX_TIMESTAMP(c) ) (
    PARTITION p0 VALUES LESS THAN (631148400),
    PARTITION p1 VALUES LESS THAN (946681200),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
(Hence, your 5.5.37 has it.)

> I'd say KEY works perfectly

OK, but only for "WHERE day_of_year = 200", not for a range of days.

> BY RANGE(DAYOFYEAR(date_date))
> WHERE day_of_year = 200
> # ...partitions: p0,p1,p2 =====>SEARCH ALL PARTITIONS? WHY?

Submit a bug at bugs.mysql.com if you like.

> WHERE DAYOFYEAR(date_date) = 200
> WHERE date_date = '2014-03-02'

In general, it is best (at least in a WHERE clause) not to hide a column inside a function.

Options: ReplyQuote


Subject
Views
Written By
Posted
2033
April 16, 2014 02:07PM
1070
April 18, 2014 05:19PM
1105
April 24, 2014 08:20PM
1050
April 26, 2014 05:46AM
Re: BETWEEN a DATE and a hard place
1198
May 02, 2014 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.