MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jon Mitchell Date:June 29 1999 2:30pm
Subject:BETWEEN fails for Timestamps
View as plain text  
>Description:

Difference in behaviour in a SELECT .. WHERE ... BETWEEN
and a SELECT ... WHERE ... <= AND <= ...

>How-To-Repeat:

I have a table which records accesses to a database:

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

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

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

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

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

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

>Fix:

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


>Submitter-Id:	<submitter ID>
>Originator:	
Jon Mitchell
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
none
>Synopsis:	
BETWEEN fails for Timestamps
>Severity:	
non-critical
>Priority:	
medium
>Category:	mysql
>Class:		
>Release:	mysql-3.22.22 (Source distribution)

>Environment:
	
System: SunOS nnsgs50 5.5 Generic_103093-25 sun4d sparc SUNW,SPARCserver-1000
Architecture: sun4

Some paths:  /usr/local/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /usr/local/tools/gnu/lib/gcc-lib/sparc-sun-solaris2.4/2.7.0/specs
gcc version 2.7.0
Compilation info: CC='gcc'  CFLAGS='-O6'  CXX='gcc'  CXXFLAGS='-O6'  LDFLAGS=''
Configure command: ./configure  --prefix=/opt/corp/tools/mysql-3.22.22
--localstatedir=/projects/FBIPass/mysql --with-unix-socket-path=/projects/FBIPass/mysql
Perl: This is perl, version 5.001
Thread
BETWEEN fails for TimestampsJon Mitchell29 Jun
  • Re: BETWEEN fails for TimestampsVivek Khera29 Jun
  • BETWEEN fails for TimestampsMichael Widenius29 Jun