When specifying a date as a string, you should always use at least 6 characters.
This is because dates, such as 98-12 or 98 , which you might assume mean
December 98 or just the year 1998, will be taken as having zero values for day,
month, and day, respectively. This would result in your date being treated as
illegal and assigned a zero value. Therefore, always remember to specify year,
month, and day, even if you are only interested in the year or the year and the
month.
All TIMESTAMP fields occupy the same amount of storage—4 bytes. You can alter a TIMESTAMP field
using ALTER TABLE without losing data. By widening a field, you will find that new data is revealed in
the previously hidden parts of the field. Likewise, if you narrow the field, you will not actually lose data;
your field will just display less information the next time you use it.
Caution MySQL will always set a TIMESTAMP column to the current time if NULL (or
NOW()) is written to it. However, if the field is left out of the list of fields when
doing an insert or update, it will only update the first TIMESTAMP field in the
table. Subsequent TIMESTAMP fields will contain the zero value (which itself is
a valid time value) if not set explicitly.Transferring Data Between Data Types
MySQL enables you to transfer data from one type to another. For example, you may have a DATETIME field
that has recorded the precise time a customer made a transaction. After a while, you may want to transfer
the data to another table but are only interested in the date of the purchase.
If you have data from a DATETIME or TIMESTAMP column and want to assign it to a DATE column, the
time information will simply be lost because the DATE type has no capacity to store time information.
Conversely, if you have a DATE value and want to write it to a DATETIME or TIMESTAMP column,
MySQL will insert 00:00:00 for the time portion because this data was not previously available.
Caution Remember that the various field types have different ranges for legal values,
and the accepting column must be able to represent the given data or it will be
lost. For example, attempting to place DATE information, where the year is not
between 1970 and 2037, into a TIMESTAMP field will cause the data to be
"zeroed."
Internal MySQL Date Functions
MySQL gives you a nice range of date and time-related functions that you can use in SELECT and WHERE
clauses.
For example, there is a MONTHNAME() function that returns the name of the month in a date field. You
can use this in a SELECT; for example, SELECT monthname(20000105) returns January. Similarly,
you can do something like the following:
mysql> SELECT mod_date FROM orders WHERE MONTHNAME(mod_date)='January';
which results in
+----------+
| mod_date |
+----------+
| 20000104 |
| 20000117 |
| 20000117 |
+----------+
3 rows in set (0.06 sec)
It's worth remembering that where MySQL expects you to pass a DATE to a function, this should be in
the form YYYYMMDD (where given as a number) or in the form YYYY-MM-DD (ideally using "-" as
delimiters, or using any other punctuation) when passed as a string.Look now at each of the functions available in a little more detail.