Featured Database Articles

Date and Time in MySQL 5 - Page 2

The effects of the SQL MODE on date and
time behavior

The concept of a
TIMESTAMP is rather different in MaxDB. With MySQL AB working towards greater
integration between MaxDB and 'ordinary' MySQL, you can now run MySQL in MAXDB
mode. In this mode, all TIMESTAMP fields are treated as ordinary DATETIME
fields. Let's run the identical commands we ran earlier, but this time in MAXDB
mode. We will change to MAXDB mode (and the later modes that follow) just for
the particular connection we're running. Quitting and reconnecting will restore
all defaults.

There is no difference
between the first and second TIMESTAMP instances, and both fields are defaulted
to NULL, with no on update.
You don't need to worry that you will experience problems with previously
defined TIMESTAMPs though - since the only real difference now between a
DATETIME and a TIMESTAMP is in their default definitions, existing TIMESTAMPS
will still behave as expected.

Another important change
is that before MySQL 5.0.2, day and month values did not have to be legal,
merely in the range 0-12, and 1-31. So, dates such as the 31st of February
could be safely stored. By default, these are now converted to 0, and throw a
warning.

If allowing invalid dates
at all disconcerts you, you can enable one of the strict modes (either STRICT_ALL_TABLES or STRICT_TRANS_TABLES), which will result
in MySQL throwing an error if the date is at all invalid.

Strict mode still permits
zero dates though. To prohibit these, you can use the NO_ZERO_DATE (the entire date cannot be
zero) or NO_ZERO_IN_DATE (no
part of the date can be zero) modes. A convenient mode to use in this case is TRADITIONAL, which is equivalent to all
of the STRICT_TRANS_TABLES,
STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO
and NO_AUTO_CREATE_USER
modes.

Conclusion

MySQL 5 has significantly
improved the flexibility of its date handling. We will also see further
examples of this in future columns. However, as we have seen, there are some
potentially nasty gotchas for users more familiar with the old ways. The
changes though do in general encourage better practices, and should be welcomed
by MySQL users.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.