<<

. 123
( 132 .)



>>

Monday if the second argument is 1.
722 Part V: Appendixes


YEAR
This function returns the year for date, in the range of 1000 to 9999:

YEAR(date) (used in examples)
RETURNS: int


YEARWEEK
This function returns the year and week for a date, in the format YYYYWW. The sec-
ond argument works exactly like the second argument in WEEK().

YEARWEEK(date [,first])
RETURNS: int


HOUR
This function returns the hour for time, in the range of 0 to 23:

HOUR(time)
RETURNS: int


MINUTE
This function returns the minute for time, in the range of 0 to 59:

MINUTE(time)
RETURNS: int


SECOND
This function returns the second for time, in the range of 0 to 59:

SECOND(time)
RETURNS: int


PERIOD_ADD
This function adds N months to period P (in the format YYMM or YYYYMM) and returns
a value in the format YYYYMM:

PERIOD_ADD(P,N)
RETURNS: int

Note that the period argument P is not a date value.
Appendix J: MySQL Function Reference 723

mysql> select period_add(200006,7);
+------------------------+
| period_add(200006,7) |
+------------------------+
| 200101 |
+------------------------+
1 row in set (0.00 sec)


PERIOD_DIFF
This function returns the number of months between periods P1 and P2. P1 and P2
should be in the format YYMM or YYYYMM.

PERIOD_DIFF(P1,P2)
RETURNS: int

Note that the period arguments P1 and P2 are not date values.

mysql> select period_diff(200106,200001);
+--------------------------------+
| period_diff(200106,200001) |
+--------------------------------+
| 17 |
+--------------------------------+
1 row in set (0.00 sec)


DATE_ADD
These functions perform date arithmetic.

DATE_ADD(date,INTERVAL expr type)

or

DATE_SUB(date,INTERVAL expr type)

or

ADDDATE(date,INTERVAL expr type)

or

SUBDATE(date,INTERVAL) (used in examples)
RETURNS: date
724 Part V: Appendixes

ADDDATE() and SUBDATE() are identical to DATE_ADD() and DATE_SUB(),
respectively. In all versions of MySQL since version 3.23 you can use + and - sym-
bols, respectively, instead of DATE_ADD() and DATE_SUB(). (See the following
example.) date is a DATETIME or DATE value specifying the starting date. expr is an
expression specifying the interval value to be added or subtracted from the starting
date. expr is a string; it may start with a - for negative intervals. type is a keyword
indicating how the expression should be interpreted.
Table J-1 shows how the type and expr arguments are related.


TABLE J-1 DATE_ADD() OPERATORS

Type Meaning Expected expr Format Value

Seconds
SECOND SS

Minutes
MINUTE MM

Minutes and seconds
MINUTE_SECOND MM:SS

Hours
HOUR HH

Hours, minutes, seconds
HOUR_SECOND HH:MM:SS

Hours and minutes
HOUR_MINUTE HH:MM

Days
DAY DAYS

Days, hours, minutes, seconds
DAY_SECOND DAYS HH:MM:SS

Days, hours, minutes
DAY_MINUTE DAYS HH:MM

Days and hours
DAY_HOUR DAYS HH

Months
MONTH MONTHS

Years
YEAR YEARS

Years and months
YEAR_MONTH YEARS-MONTHS




MySQL allows any punctuation delimiter in the expr format. The ones shown in
the table are the suggested delimiters. If the date argument is a DATE value and
your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts),
the result is a DATE value. Otherwise, the result is a DATETIME value.
Appendix J: MySQL Function Reference 725

mysql> select ˜2001-01-01 13:00:00™ + interval 10 m
+--------------------------------------------+
| ˜2001-01-01 13:00:00™ + interval 10 minute |
+--------------------------------------------+
| 2001-01-01 13:10:00 |
+--------------------------------------------+
1 row in set (0.39 sec)

mysql> select ˜2000-01-01 00:00:00™ - interval 1 second;
+-------------------------------------------+
| ˜2000-01-01 00:00:00™ - interval 1 second |
+-------------------------------------------+
| 1999-12-31 23:59:59 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(˜2000-01-01 00:00:00™, interval ˜1:1:1™
hour_second);
+---------------------------------------------------------------+
| date_add(˜2000-01-01 00:00:00™, interval ˜1:1:1™ hour_second) |
+---------------------------------------------------------------+
| 2000-01-01 01:01:01 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(˜2000-01-01 00:00:00™, interval ˜1™ month);
+-----------------------------------------------------+
| date_sub(˜2000-01-01 00:00:00™, interval ˜1™ month) |
+-----------------------------------------------------+
| 1999-12-01 00:00:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

If you specify an interval value that is too short (one that does not include all the
interval parts that would be expected from the type keyword), MySQL assumes you
have left out the leftmost parts of the interval value. For example, if you specify a
type of DAY_SECOND, the value of expr is expected to have days, hours, minutes,
and seconds parts. If you specify a value like 1:10, MySQL assumes that the days
and hours parts are missing and that the value represents minutes and seconds.
726 Part V: Appendixes


TO_DAYS
Given a date date, this function returns a daynumber (the number of days since
year 0):

TO_DAYS(date) (used in examples)
RETURNS: int



mysql> select to_days(˜2003-01-01™);
+-----------------------+
| to_days(˜2003-01-01™) |
+-----------------------+
| 731581 |
+-----------------------+

TO_DAYS() is not intended for use with values that precede the advent of the
Gregorian calendar (1582). Note that it is not the same as the PHP mktime() func-
tion, which gets the date relative to January 1, 1970. See the entry for the MySQL
UNIX_TIMESTAMP function, later in this appendix, if you need that information.


FROM_DAYS
Given a daynumber N, this function returns a DATE calculated from year 0:

FROM_DAYS(N) (used in examples)
RETURNS: date

mysql> select from_days(˜731581™);
+---------------------+
| from_days(˜731581™) |
+---------------------+
| 2003-01-01 |
+---------------------+

FROM_DAYS() is not intended for use with values that precede the advent of the
Gregorian calendar (1582).

DATE_FORMAT
This function formats the date value according to the format string:

DATE_FORMAT(date,format) (used in examples)
RETURNS: string
Appendix J: MySQL Function Reference 727

The specifiers in Table J-2 can be used in the format string.


TABLE J-2 DATE_FORMAT SPECIFIERS

Specifier Meaning

Month name (January through December)
%M

Weekday name (Sunday through Saturday)
%W

Day of the month with English ordinal suffix (1st, 2nd, 3rd, and so on)
%D

Year, numeric, four digits
%Y

Year, numeric, two digits
%y

Abbreviated weekday name (Sun...Sat)
%a

<<

. 123
( 132 .)



>>