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
