Interestingly, use of date_add() and date_sub() on 'odd' dates such as "Feb 31" does produce sane results. Subtract one from "2000 Feb 31", and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
mysql> select from_unixtime(unix_timestamp('2000-11-31'),'%Y-%m-%d');
+--------------------------------------------------------+
| from_unixtime(unix_timestamp('2000-11-31'),'%Y-%m-%d') |
+--------------------------------------------------------+
| 2000-12-01 |
+--------------------------------------------------------+mysql> select from_unixtime(unix_timestamp('2000-02-31'),'%Y-%m-%d');
+--------------------------------------------------------+
| from_unixtime(unix_timestamp('2000-02-31'),'%Y-%m-%d') |
+--------------------------------------------------------+
| 2000-03-02 |
+--------------------------------------------------------+And lastly, I don't know how this happens:
mysql> select unix_timestamp('2002-1111102-31');
+-----------------------------------+
| unix_timestamp('2002-1111102-31') |
+-----------------------------------+
| 1037026951 |
+-----------------------------------+We could then probably eliminate all timestamps stored as 1969-12-31 as we know that they're illegal.
-- [ Rajesh Kumar ] __________________________________________ Meet the guy at http://www.meetRajesh.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
