results in
+-----------------------+
| PERIOD_ADD(200004,-2) |
+-----------------------+
| 200002 |
+-----------------------+
Note PERIOD_ADD() deals with years and months but does not take a date
parameter. If you try to pass it a date formatted in DATE format, or NOW(), you will
get an invalid result.
For finding the difference between two time periods, you can use PERIOD_DIFF(period1,period2).
Try to calculate how many months between January 1980 and June 2000:
mysql> SELECT PERIOD_DIFF(200006,198001);results in
+----------------------------+
| PERIOD_DIFF(200006,198001) |
+----------------------------+
| 245 |
+----------------------------+
As with PERIOD_ADD(), PERIOD_DIFF()expects YYYYMM or YYMM and not the normal date format.Summary
In this chapter, you've examined the formats that MySQL uses for formatting dates and times. It is relaxed in
accepting date and time information with a range of punctuation and field lengths, and interprets what you
give it according to a set of rules. However, it outputs the same data in more predictable formats and
accommodates both string and numeric formats according to how you're using the result.
You've looked at several formats:
DATETIME For combined date and time information
DATE For date information only
TIME For time information only
YEAR For year information only
TIMESTAMP Records date and time and, by default, will automatically set itself to the
creation or last modification date of a rowYou've also covered a number of functions for extracting both numerical and textual information on
times and dates. You've seen how you can do date arithmetic using MySQL's tools for adding and
subtracting time intervals, and even for finding the difference in time between two given months.Q&A
Q:
What is the best way to get the present system time and insert it into
my data?
A:
The present system time can easily be obtained using the NOW() function,
which will return the date and time. You can insert this into any of the date
and time fields in your table. However, you can also declare a column to be
of the TIMESTAMP type, which will cause this column to be updated each
time you write data to that row, provided you don't write anything to that field
explicitly, since doing this will put specific data into the field.
Q: How can I display times and dates in a user-friendly format?
A:
Use the DATE_FORMAT()functions. These offer great flexibility in formatting
date and time information according to the needs of the user.
Q: What's the best format for specifying dates and times to MySQL?
A:
MySQL is very relaxed in that it can accept date and time data in a wide
range of styles, delimited with punctuation marks or not delimited at all. For
the sake of clarity, it may be best practice to submit your data in the same