Re: Possible date bug

2002-12-10 Thread Bruce MacDonald 
John,

 Am I mistaken or does 2002-02-31 translate into February 31, 2002? 

I see it now -- right there on Page 646 of the MySQL Manual:

| Note that MySQL does no checking whether the date is correct. 
| If you store an incorrect date, such as '1998-2-31', 
| the wrong date will be stored. If the date cannot be converted 
| to any reasonable value, a 0 is stored in the DATE field. 
| This is mainly a speed issue and we think it is up to the 
| application to check the dates, and not the server.

Bruce MacDonald
Minnesota Public Radio


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Possible date bug

2002-12-05 Thread Bruce MacDonald 
John,

 Am I mistaken or does 2002-02-31 translate into February 31, 2002? If it
does ...

Your observation is interesting.

The MySQL manual says the query should set all zeroes if the date value is
illegal. This does happen if you enter 32 days for a month, for example.

Furthermore, I got two different unix_timestamp values in separate
experiments when I queried the 31st-of-February date:

mysql select td, unix_timestamp(td), from_unixtime(unix_timestamp(td)) from
mine;
+-++
---+
| td  | unix_timestamp(td) |
from_unixtime(unix_timestamp(td)) |
+-++
---+
| 2002-02-31 00:00:00 | 1014962400 | 2002-03-01 00:00:00
|
| 2002-11-31 00:00:00 | 1038722400 | 2002-12-01 00:00:00
|
+-++
---+
2 rows in set (0.00 sec)

** Note it says March 1st in the third column.

Then I decided to add a few more illegal values and do another from_unixtime
conversion...

mysql insert into mine values (2002-02-29);
Query OK, 1 row affected (0.01 sec)

mysql insert into mine values (2002-02-30);
Query OK, 1 row affected (0.00 sec)

mysql select td, unix_timestamp(td), from_unixtime(unix_timestamp(td)) from
mine;
+-++
---+
| td  | unix_timestamp(td) |
from_unixtime(unix_timestamp(td)) |
+-++
---+
| 2002-02-31 00:00:00 | 1015135200 | 2002-03-03 00:00:00
|
| 2002-11-31 00:00:00 | 1038722400 | 2002-12-01 00:00:00
|
| 2002-02-29 00:00:00 | 1014962400 | 2002-03-01 00:00:00
|
| 2002-02-30 00:00:00 | 1015048800 | 2002-03-02 00:00:00
|
+-++
---+
4 rows in set (0.00 sec)

** Note that the unix_timestamp is different this time for the Feb 31st
date and that it says March 3rd in the third column.

I am running version  3.23.51 on Red Hat Linux release 6.0 (Hedwig) Kernel
2.2.5-15 on an i486.

I have a scheduling application that uses unix_timestamps throughout (stored
as unsigned INTs instead of DATETIMEs), and I haven't noticed any such
problem with illegal date values.

There must be an explanation but scouring the books and manual I could not
find one.

Bruce MacDonald
Minnesota Public Radio

- Original Message -
From: John Griffin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, December 04, 2002 3:35 PM
Subject: Possible date bug


Hi All,

I ran the following commands:

DROP TABLE IF EXISTS test_date;
CREATE TABLE test_date (test_date datetime);
INSERT INTO test_date (test_date) VALUES ('2002-02-31');
SELECT * FROM test_date;

I got the following results:

mysql DROP TABLE IF EXISTS test_date;
Query OK, 0 rows affected (0.02 sec)

mysql CREATE TABLE test_date (test_date datetime);
Query OK, 0 rows affected (0.03 sec)

mysql INSERT INTO test_date (test_date) VALUES ('2002-02-31');
Query OK, 1 row affected (0.01 sec)

mysql SELECT * FROM test_date;
+-+
| test_date   |
+-+
| 2002-02-31 00:00:00 |
+-+
1 row in set (0.00 sec)

mysql

Am I mistaken or does 2002-02-31 translate into February 31, 2002? If it
does ...

I am running MySQL 3.23.41 on Windows 2000.

John


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Possible date bug

2002-12-04 Thread John Griffin 
Hi All,

I ran the following commands:

DROP TABLE IF EXISTS test_date;
CREATE TABLE test_date (test_date datetime);
INSERT INTO test_date (test_date) VALUES ('2002-02-31');
SELECT * FROM test_date;

I got the following results:

mysql DROP TABLE IF EXISTS test_date;
Query OK, 0 rows affected (0.02 sec)

mysql CREATE TABLE test_date (test_date datetime);
Query OK, 0 rows affected (0.03 sec)

mysql INSERT INTO test_date (test_date) VALUES ('2002-02-31');
Query OK, 1 row affected (0.01 sec)

mysql SELECT * FROM test_date;
+-+
| test_date   |
+-+
| 2002-02-31 00:00:00 |
+-+
1 row in set (0.00 sec)

mysql

Am I mistaken or does 2002-02-31 translate into February 31, 2002? If it does ...

I am running MySQL 3.23.41 on Windows 2000.

John


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php