12 messages in com.mysql.lists.mysqlRe: Dates querying Mysql
FromSent OnAttachments
Claudia M. Castaneda24 May 1999 12:52 
Graeme B. Davis24 May 1999 13:46 
Christian Mack25 May 1999 10:28 
Claudia M. Castaneda25 May 1999 11:17 
Jim Faucette25 May 1999 11:29 
Claudia M. Castaneda25 May 1999 11:46 
Vivek Khera25 May 1999 12:04 
Jim Faucette25 May 1999 12:47 
Michael Widenius30 May 1999 05:31 
Vivek Khera02 Jun 1999 07:21 
Vivek Khera02 Jun 1999 11:14 
Michael Widenius02 Jun 1999 11:26 
Subject:Re: Dates querying Mysql
From:Jim Faucette (ji@awod.com)
Date:05/25/1999 12:47:03 PM
List:com.mysql.lists.mysql

Vivek Khera wrote:

"CMC" == Claudia M Castaneda <clau@shiny.gsfc.nasa.gov> writes:

CMC> Well, it worked!!

CMC> Now, if you would be kind enough to explain us what is in a +0???

For the same reason that

select obs_id, obs_URI, obs_source, beg_date, beg_time from observation where beg_date between '1999-01-20' and '1999-05-30';

will work. The automagic type conversion from strings to numbers in MySQL seems to fail in this instance when comparing a string type to numbers with "BETWEEN".

By adding the +0, you explicitly converted the date to a number type. In my example, I made the values for the BETWEEN into strings, which are treated nicely as DATEs.

I don't know which is faster, though.

Since MySQL stores dates as numbers (long longs?), but returns them as strings the ntoa conversion is still needed. However, +0 may save a string formatting step.

(Awaiting Monty's thoughts :)

jim...

Claudia, here some examples of +0 forcing results to ints: mysql> select "test"; +------+ | test | +------+ | test | +------+ 1 row in set (0.00 sec)

mysql> select "test"+0; +----------+ | "test"+0 | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)

mysql> select "100test"+0; +-------------+ | "100test"+0 | +-------------+ | 100 | +-------------+ 1 row in set (0.00 sec)

mysql> select "100.001test"+0; +-----------------+ | "100.001test"+0 | +-----------------+ | 100 | +-----------------+ 1 row in set (0.01 sec)