>>>>> "Grzegorz" == Grzegorz BRZESKI <[EMAIL PROTECTED]> writes:
Grzegorz> Hi once again,
Grzegorz> I have found very strange behaviour (in my opinnion) of function
Grzegorz> DATE_ADD and DATE_SUB with NOW() function. Simple example:
Grzegorz> Table 'test' with just one column and let say 10 records:
Grzegorz> id int
Grzegorz> Query:
Grzegorz> SELECT date_sub( now(), interval 3 day) FROM test
Grzegorz> Result:
Grzegorz> 1999-01-19 02:25:59
Grzegorz> 1999-01-22 02:25:59
Grzegorz> 1999-01-25 02:25:59
Grzegorz> 1999-01-28 02:25:59
Grzegorz> 1999-01-31 02:25:59
Grzegorz> 1999-02-03 02:25:59
Grzegorz> 1999-02-06 02:25:59
Grzegorz> 1999-02-09 02:25:59
Grzegorz> 1999-02-12 02:25:59
Grzegorz> 1999-02-15 02:25:59
Grzegorz> Shouldn't all the dates be the same ? The hh:dd:ss time is fine but
Grzegorz> YYYY-MM-DD date is out of order in my opinnion. When I use
Grzegorz> CURRENT_DATE() instead of NOW() all is fine. But NOT always I can use
Grzegorz> CURRENT_DATE() as there is some other buq with DATE_ADD(), DATE_SUB()
Grzegorz> which I don't remember now (will look ip up, promise).
Grzegorz> If anybody is got any ideas, or I have made some mistake please let me
Grzegorz> know.
Hi!
The above bug is because now() uses a static string and date_sub()
destroys the original value in this context.
Here is a patch for this:
((/my/monty/sql)) diff -c /my/monty/master/mysql-3.22.14-gamma/sql/item_timefunc
.h .
*** /my/monty/master/mysql-3.22.14-gamma/sql/item_timefunc.h Mon Dec 14 20:05:19
1998
--- ./item_timefunc.h Sat Jan 16 12:10:40 1999
***************
*** 229,235 ****
enum Item_result result_type () const { return STRING_RESULT; }
double val() { return (double) value; }
longlong val_int() { return value; }
! String *str(String *str) { str->set(buff,buff_length); return str; }
const char *func_name() const { return "curtime"; }
void fix_length_and_dec();
};
--- 229,236 ----
enum Item_result result_type () const { return STRING_RESULT; }
double val() { return (double) value; }
longlong val_int() { return value; }
! String *str(String *str)
! { str_value.set(buff,buff_length); return &str_value; }
const char *func_name() const { return "curtime"; }
void fix_length_and_dec();
};
***************
*** 257,263 ****
enum Item_result result_type () const { return STRING_RESULT; }
double val() { return (double) value; }
longlong val_int() { return value; }
! String *str(String *str) { str->set(buff,buff_length); return str; }
const char *func_name() const { return "now"; }
void fix_length_and_dec();
};
--- 258,265 ----
enum Item_result result_type () const { return STRING_RESULT; }
double val() { return (double) value; }
longlong val_int() { return value; }
! String *str(String *str)
! { str_value.set(buff,buff_length); return &str_value; }
const char *func_name() const { return "now"; }
void fix_length_and_dec();
};
(The above patch will be in MySQL 3.22.15 :)
Regards,
Monty
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
