NYCPHP Meetup

[Hans Zaunere]

> I recently upgraded MySQL to 4.1.8 and had some PHP applications break
> because of changes in date handling, so I ended up rolling back to the
> older version. My question is, is it possible to enable backwards
> compatibility for 4.1.8 especially wrt date handling???

>From http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html

For functions that produce a DATE, DATETIME, or TIME value, the result
returned to the client now is fixed up to have a temporal type. For
example, in MySQL 4.1, you get this result:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
       -> '2001-01-01 00:00:00'

In MySQL 4.0, the result is different:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
       -> '2001-01-01'

AND:

Incompatible change: TIMESTAMP is now returned as a string in
'YYYY-MM-DD HH:MM:SS' format (from 4.0.12 the --new option can be used
to make a 4.0 server behave as 4.1 in this respect). See section
11.3.1.2 TIMESTAMP Properties as of MySQL 4.1. If you want to have the
value returned as a number (as MySQL 4.0 does) you should add +0 to
TIMESTAMP columns when you retrieve them:

mysql> SELECT ts_col + 0 FROM tbl_name;

Display widths for TIMESTAMP columns are no longer supported. For
example, if you declare a column as TIMESTAMP(10), the (10) is ignored.
These changes were necessary for SQL standards compliance. In a future
version, a further change will be made (backward compatible with this
change), allowing the timestamp length to indicate the desired number of
digits for fractions of a second.


So yeah, date stuff has changed a bit.  Unfortunately there isn't a way
to get 4.1 to behave as 4.0 in these respects.  However, you can get 4.0
to act as 4.1 to ease upgrading.  Check out the --new option as
described in the above link, and here:

http://dev.mysql.com/doc/mysql/en/Server_options.html


---
Hans Zaunere, Sales Engineer
MySQL, Inc.  www.mysql.com
Office: +1 212.213.1131

Are you MySQL certified?
www.mysql.com/certification

President, Founder
New York PHP
http://www.nyphp.org