Re: BETWEEN a DATE and a hard place

[Gerald Bayles]

Hi Rick and thank you for your help.

The number of records ranges from two million to the biggest number we can efficiently store on an AWS X1-Large. The numbers are not cast in stone.

We are using partitions to keep a number of days of storage for our customers. We want to offer them the highest number of days possible.

Yes, dev_tz_date is INDEXed. It is a DATE type and, in the snippet shown, would uniquely identify a particular day within the last 100 days (going past 100 would, obviously, wrap around randomly.

I wanted to use a RANGE PARTITION but find the syntax too cumbersome:
PARTITION BY RANGE ( DAYOFYEAR(dev_tz_date) % 100 ) (
PARTITION p0 VALUES LESS THAN (0),
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
. . .
PARTITION p3 VALUES LESS THAN 100
);
Is there another way to specify RANGE partitions?


Since we are restricting searches to an arbitrary but contiguous one week block, I am not averse to using many 'OR's but prefer the simplicity of BETWEEN.


Thanks