List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 29 1999 6:23pm
Subject:BETWEEN fails for Timestamps
View as plain text  
>>>>> "Jon" == Jon Mitchell <jonm@stripped> writes:

>> Description:
Jon> Difference in behaviour in a SELECT .. WHERE ... BETWEEN
Jon> and a SELECT ... WHERE ... <= AND <= ...

>> How-To-Repeat:

Jon> I have a table which records accesses to a database:

mysql> describe Requests;
Jon> +---------+---------------+------+-----+---------+-------+
Jon> | Field   | Type          | Null | Key | Default | Extra |
Jon> +---------+---------------+------+-----+---------+-------+
Jon> | EmpID   | char(7)       | YES  |     | NULL    |       |
Jon> | PatchID | char(10)      | YES  |     | NULL    |       |
Jon> | CLLI    | char(10)      | YES  |     | NULL    |       |
Jon> | When    | timestamp(14) | YES  |     | NULL    |       |
Jon> | Success | char(1)       | YES  |     | NULL    |       |
Jon> +---------+---------------+------+-----+---------+-------+
Jon> 5 rows in set (0.00 sec)

Jon> This table is full with lots of information. To query I
Jon> can use:

mysql> select * from Requests where '19990519' <= When and When <= '19990521';

Jon> Which extracts about a dozen records (not reproduced). However
Jon> I'd prefer to use:

mysql> select * from Requests where When between '19990519' and '19990521';

Jon> This just returns an empty set, whereas I expect it to produce
Jon> the same dozen records

>> Fix:

Jon> Current Fix, just use the first version of the SELECT.

Hi!

The problem is that BETWEEN doesn't have a auto conversion of dates as
<= and >= has.

Your first query is actually illegal.  It should be:

select * from Requests where '1999-05-19' <= When and When <= '1999-05-21'

The following should also work:

select * from Requests where When between '1999-05-19' and '1999-05-21';

Regards,
Monty
Thread
BETWEEN fails for TimestampsJon Mitchell29 Jun
  • Re: BETWEEN fails for TimestampsVivek Khera29 Jun
  • BETWEEN fails for TimestampsMichael Widenius29 Jun