I have a table with a DATE column.
I want to get a record set for all rows for this month.

select * from TheTable where MeetingDate like '2011-11';
select * from TheTable where MeetingDate between '2011-11-01' and
'2011-11-31';

Does it matter if there is no 31st of the month?

Try:
select * from TheTable where MeetingDate like '2011-11-%';

This is likely to use an index on MeetingDate if there is one.


Also:
select * from TheTable where year(MeetingDate) = '2011' and
month(MeetingDate) = '11';

This is less likely to use an index on MeetingDate if there is one.

On Nov 7, 11:43*am, gordonb.lk... (AT) burditt (DOT) org (Gordon Burditt) wrote:
Many thanks.

On 07-11-2011 19:21, SpreadTooThin wrote:
select * from TheTable
where MeetingDate between '2011-11-01' and last_day('2011-11-01');

http://dev.mysql.com/doc/refman/5.5/...ction_last-day

--
Luuk

Luuk <Luuk (AT) invalid (DOT) lan> wrote:
+1

LIKE should not be used because a DATE is internally stored as a DATE
and *not* as a string. In order to check the LIKE predicate, MySQL has
to convert the DATE to a string and thus cannot use an index lookup.

Giving two date literals is OK because since those are constants, they
can be converted to DATEs and then used for a range scan on the index.


Example:

mysql>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` date DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql>select count(*) from t1;
+----------+

mysql>explain select count(*) from t1 where c1 between "2011-11-1" and "2011-11-30"\G
*************************** 1. row ***************************
table: t1
type: range
key: c1
rows: 3
Extra: Using where; Using index

mysql>explain select count(*) from t1 where c1 like "2011-11-%"\G
*************************** 1. row ***************************
table: t1
type: index
key: c1
rows: 9
Extra: Using where; Using index


The first query does a range scan on the index which will be the
fastest possible plan for this type of query. The second query does
an index scan and will scan *all* rows from the table. And for each
row there are costs to convert the date to string and check the LIKE.


XL

On Nov 7, 4:06*pm, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:

If this is to be used for more than one type of report, you could
always use a "virtual column".

alter table xyz add column c virtual int as year(MeetingDate) =
'2011' , d virtual int as month(MeetingDate) = '11', e virtual int as
day(MeetingDate);
create index cd on xyz (c,d);

I am not sure 100% sure about MySQL , but, this should use the index
when using it
select * from xyz where c=2011 and d=11 order by e;

onedbguru <onedbguru (AT) yahoo (DOT) com> wrote:

[snip]

No, you cannot. MySQL does not support virtual columns.
Even if it would - this would be rather useless data duplication.
A simple index on the DATE column does the trick.


XL

On Nov 8, 2:59*am, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:


Ahh... I was looking at something "future"???

http://forge.mysql.com/wiki/MySQL_vi...mns_ref_manual

On 09-11-2011 02:36, onedbguru wrote:
Its not that abvious from that page alone, but the seem to be wanting
this in MySQL 6.0
(quick conclusion based on last line on that page)


--
Luuk

onedbguru <onedbguru (AT) yahoo (DOT) com> wrote:
Kind of.

Scroll to the bottom of the page.

"Source code on the Launchpad:
https://code.launchpad.net/~andrey-zhakov/mysql-server/mysql-6.0-wl1075-wl411"

So it's a private branch of a guy named Andrey Zhakov who (according to
his LinkedIn profile) has never worked for MySQL AB/Sun/Oracle. Looks
like he wanted to contribute to MySQL 6.0 by implementing worklogs 411
and 1075. Looking at the "community feedback" tab on

http://forge.mysql.com/worklog/task.php?id=411

and then at

http://bugs.mysql.com/bug.php?id=46491

this is confirmed.

When the release plan was changed (years ago actually) to make the next
release after 5.1 not 6.0 but 5.5, features have been backported from
6.0 to the new 5.5 tree. However this patch has not made it to 5.5.
It's not in the 5.6 manual either:

http://dev.mysql.com/doc/refman/5.6/...ate-table.html

so it didn't make it there, too. The patch however made it to Drizzle,
to MariaDB and probably to Percona Server.


Things you should have learned now:

- always read the complete page
- don't trust a public Wiki too much
- the MySQL manual is definitive


XL