(mysql.info.gz) Date and time functions
Info Catalog
(mysql.info.gz) Numeric Functions
(mysql.info.gz) Functions
(mysql.info.gz) Fulltext Search
12.5 Date and Time Functions
============================
This section describes the functions that can be used to manipulate
temporal values. See Date and time types for a description of
the range of values each date and time type has and the valid formats
in which values may be specified.
Here is an example that uses date functions. The following query
selects all records with a `date_col' value from within the last 30
days:
mysql> SELECT SOMETHING FROM TBL_NAME
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE_COL;
Note that the query also will select records with dates that lie in the
future.
Functions that expect date values usually will accept datetime values
and ignore the time part. Functions that expect time values usually will
accept datetime values and ignore the date part.
Functions that return the current date or time each are evaluated only
once per query at the start of query execution. This means that
multiple references to a function such as `NOW()' within a single query
will always produce the same result. This principle also applies to
`CURDATE()', `CURTIME()', `UTC_DATE()', `UTC_TIME()', `UTC_TIMESTAMP()',
and to any of their synonyms.
Beginning with MySQL 4.1.3, the `CURRENT_TIMESTAMP()',
`CURRENT_TIME()', `CURRENT_DATE()', and `FROM_UNIXTIME()' functions
return values in the connection's current time zone, which is available
as the value of the `time_zone' system variable. Also,
`UNIX_TIMESTAMP()' assumes that its argument is a datetime value in the
current time zone. Time zone support.
The return value ranges in the following function descriptions apply for
complete dates. If a date is a "zero" value or an incomplete date such
as `'2001-11-00'', functions that extract a part of a date may return
`0'. For example, `DAYOFMONTH('2001-11-00')' returns `0'.
`ADDDATE(DATE,INTERVAL EXPR TYPE)'
`ADDDATE(EXPR,DAYS)'
When invoked with the `INTERVAL' form of the second argument,
`ADDDATE()' is a synonym for `DATE_ADD()'. The related function
`SUBDATE()' is a synonym for `DATE_SUB()'. For information on the
`INTERVAL' argument, see the discussion for `DATE_ADD()'.
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
As of MySQL 4.1.1, the second syntax is allowed, where EXPR is a
date or datetime expression and DAYS is the number of days to be
added to EXPR.
mysql> SELECT ADDDATE('1998-01-02', 31);
-> '1998-02-02'
`ADDTIME(EXPR,EXPR2)'
`ADDTIME()' adds EXPR2 to EXPR and returns the result. EXPR is a
time or datetime expression, and EXPR2 is a time expression.
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',
-> '1 1:1:1.000002');
-> '1998-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
-> '03:00:01.999997'
`ADDTIME()' was added in MySQL 4.1.1.
`CONVERT_TZ(DT,FROM_TZ,TO_TZ)'
`CONVERT_TZ()' converts a datetime value DT from time zone given
by FROM_TZ to the time zone given by TO_TZ and returns the
resulting value. Time zones may be specified as described in
Time zone support. This function returns `NULL' if the
arguments are invalid.
If the value falls out of the supported range of the `TIMESTAMP'
type when converted fom FROM_TZ to UTC, no conversion occurs. The
`TIMESTAMP' range is described at Date and time type
overview.
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
-> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','-07:00');
-> '2004-01-01 05:00:00'
To use named time zones such as `'MET'' or `'Europe/Moscow'', the
time zone tables must be properly set up. See Time zone
support for instructions.
`CONVERT_TZ()' was added in MySQL 4.1.3.
`CURDATE()'
Returns the current date as a value in `'YYYY-MM-DD'' or `YYYYMMDD'
format, depending on whether the function is used in a string or
numeric context.
mysql> SELECT CURDATE();
-> '1997-12-15'
mysql> SELECT CURDATE() + 0;
-> 19971215
`CURRENT_DATE'
`CURRENT_DATE()'
`CURRENT_DATE' and `CURRENT_DATE()' are synonyms for `CURDATE()'.
`CURTIME()'
Returns the current time as a value in `'HH:MM:SS'' or `HHMMSS'
format, depending on whether the function is used in a string or
numeric context.
mysql> SELECT CURTIME();
-> '23:50:26'
mysql> SELECT CURTIME() + 0;
-> 235026
`CURRENT_TIME'
`CURRENT_TIME()'
`CURRENT_TIME' and `CURRENT_TIME()' are synonyms for `CURTIME()'.
`CURRENT_TIMESTAMP'
`CURRENT_TIMESTAMP()'
`CURRENT_TIMESTAMP' and `CURRENT_TIMESTAMP()' are synonyms for
`NOW()'.
`DATE(EXPR)'
Extracts the date part of the date or datetime expression EXPR.
mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
`DATE()' is available as of MySQL 4.1.1.
`DATEDIFF(EXPR,EXPR2)'
`DATEDIFF()' returns the number of days between the start date
EXPR and the end date EXPR2. EXPR and EXPR2 are date or
date-and-time expressions. Only the date parts of the values are
used in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
`DATEDIFF()' was added in MySQL 4.1.1.
`DATE_ADD(DATE,INTERVAL EXPR TYPE)'
`DATE_SUB(DATE,INTERVAL EXPR TYPE)'
These functions perform date arithmetic. 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.
The `INTERVAL' keyword and the `type' specifier are not case
sensitive.
The following table shows how the TYPE and EXPR arguments are
related:
TYPE *Value* *Expected* EXPR *Format*
`MICROSECOND' `MICROSECONDS'
`SECOND' `SECONDS'
`MINUTE' `MINUTES'
`HOUR' `HOURS'
`DAY' `DAYS'
`WEEK' `WEEKS'
`MONTH' `MONTHS'
`QUARTER' `QUARTERS'
`YEAR' `YEARS'
`SECOND_MICROSECOND' `'SECONDS.MICROSECONDS''
`MINUTE_MICROSECOND' `'MINUTES.MICROSECONDS''
`MINUTE_SECOND' `'MINUTES:SECONDS''
`HOUR_MICROSECOND' `'HOURS.MICROSECONDS''
`HOUR_SECOND' `'HOURS:MINUTES:SECONDS''
`HOUR_MINUTE' `'HOURS:MINUTES''
`DAY_MICROSECOND' `'DAYS.MICROSECONDS''
`DAY_SECOND' `'DAYS HOURS:MINUTES:SECONDS''
`DAY_MINUTE' `'DAYS HOURS:MINUTES''
`DAY_HOUR' `'DAYS HOURS''
`YEAR_MONTH' `'YEARS-MONTHS''
The TYPE values `DAY_MICROSECOND', `HOUR_MICROSECOND',
`MINUTE_MICROSECOND', `SECOND_MICROSECOND', and `MICROSECOND' are
allowed as of MySQL 4.1.1. The values `QUARTER' and `WEEK' are
allowed as of MySQL 5.0.0.
MySQL allows any punctuation delimiter in the EXPR format. Those
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.
As of MySQL 3.23, `INTERVAL EXPR TYPE' is allowed on either side
of the `+' operator if the expression on the other side is a date
or datetime value. For the `-' operator, `INTERVAL EXPR TYPE' is
allowed only on the right side, because it makes no sense to
subtract a date or datetime value from an interval. (See examples
below.)
mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
-> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
-> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
If you specify an interval value that is too short (does not
include all the interval parts that would be expected from the
TYPE keyword), MySQL assumes that 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 the value represents minutes and seconds. In other words,
`'1:10' DAY_SECOND' is interpreted in such a way that it is
equivalent to `'1:10' MINUTE_SECOND'. This is analogous to the
way that MySQL interprets `TIME' values as representing elapsed
time rather than as time of day.
If you add to or subtract from a date value something that
contains a time part, the result is automatically converted to a
datetime value:
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
-> '1999-01-02'
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
-> '1999-01-01 01:00:00'
If you use really malformed dates, the result is `NULL'. If you add
`MONTH', `YEAR_MONTH', or `YEAR' and the resulting date has a day
that is larger than the maximum day for the new month, the day is
adjusted to the maximum days in the new month:
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> '1998-02-28'
`DATE_FORMAT(DATE,FORMAT)'
Formats the DATE value according to the FORMAT string. The
following specifiers may be used in the FORMAT string:
*Specifier* *Description*
`%a' Abbreviated weekday name (`Sun'..`Sat')
`%b' Abbreviated month name (`Jan'..`Dec')
`%c' Month, numeric (`0'..`12')
`%D' Day of the month with English suffix (`0th',
`1st', `2nd', `3rd', ...)
`%d' Day of the month, numeric (`00'..`31')
`%e' Day of the month, numeric (`0'..`31')
`%f' Microseconds (`000000'..`999999')
`%H' Hour (`00'..`23')
`%h' Hour (`01'..`12')
`%I' Hour (`01'..`12')
`%i' Minutes, numeric (`00'..`59')
`%j' Day of year (`001'..`366')
`%k' Hour (`0'..`23')
`%l' Hour (`1'..`12')
`%M' Month name (`January'..`December')
`%m' Month, numeric (`00'..`12')
`%p' `AM' or `PM'
`%r' Time, 12-hour (`hh:mm:ss' followed by `AM' or
`PM')
`%S' Seconds (`00'..`59')
`%s' Seconds (`00'..`59')
`%T' Time, 24-hour (`hh:mm:ss')
`%U' Week (`00'..`53'), where Sunday is the first
day of the week
`%u' Week (`00'..`53'), where Monday is the first
day of the week
`%V' Week (`01'..`53'), where Sunday is the first
day of the week; used with `%X'
`%v' Week (`01'..`53'), where Monday is the first
day of the week; used with `%x'
`%W' Weekday name (`Sunday'..`Saturday')
`%w' Day of the week (`0'=Sunday..`6'=Saturday)
`%X' Year for the week where Sunday is the first
day of the week, numeric, four digits; used
with `%V'
`%x' Year for the week, where Monday is the first
day of the week, numeric, four digits; used
with `%v'
`%Y' Year, numeric, four digits
`%y' Year, numeric, two digits
`%%' A literal `%'.
All other characters are copied to the result without
interpretation.
The `%v', `%V', `%x', and `%X' format specifiers are available as
of MySQL 3.23.8. `%f' is available as of MySQL 4.1.1.
As of MySQL 3.23, the `%' character is required before format
specifier characters. In earlier versions of MySQL, `%' was
optional.
The reason the ranges for the month and day specifiers begin with
zero is that MySQL allows incomplete dates such as `'2004-00-00''
to be stored as of MySQL 3.23.
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
`DAY(DATE)'
`DAY()' is a synonym for `DAYOFMONTH()'. It is available as of
MySQL 4.1.1.
`DAYNAME(DATE)'
Returns the name of the weekday for DATE.
mysql> SELECT DAYNAME('1998-02-05');
-> 'Thursday'
`DAYOFMONTH(DATE)'
Returns the day of the month for DATE, in the range `1' to `31'.
mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3
`DAYOFWEEK(DATE)'
Returns the weekday index for DATE (`1' = Sunday, `2' = Monday,
..., `7' = Saturday). These index values correspond to the ODBC
standard.
mysql> SELECT DAYOFWEEK('1998-02-03');
-> 3
`DAYOFYEAR(DATE)'
Returns the day of the year for DATE, in the range `1' to `366'.
mysql> SELECT DAYOFYEAR('1998-02-03');
-> 34
`EXTRACT(TYPE FROM DATE)'
The `EXTRACT()' function uses the same kinds of interval type
specifiers as `DATE_ADD()' or `DATE_SUB()', but extracts parts
from the date rather than performing date arithmetic.
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
-> 20102
mysql> SELECT EXTRACT(MICROSECOND
-> FROM '2003-01-02 10:30:00.00123');
-> 123
`EXTRACT()' was added in MySQL 3.23.0.
`FROM_DAYS(N)'
Given a daynumber N, returns a `DATE' value.
mysql> SELECT FROM_DAYS(729669);
-> '1997-10-07'
`FROM_DAYS()' is not intended for use with values that precede the
advent of the Gregorian calendar (1582), because it does not take
into account the days that were lost when the calendar was changed.
`FROM_UNIXTIME(UNIX_TIMESTAMP)'
`FROM_UNIXTIME(UNIX_TIMESTAMP,FORMAT)'
Returns a representation of the UNIX_TIMESTAMP argument as a value
in `'YYYY-MM-DD HH:MM:SS'' or `YYYYMMDDHHMMSS' format, depending on
whether the function is used in a string or numeric context.
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
If FORMAT is given, the result is formatted according to the
FORMAT string. FORMAT may contain the same specifiers as those
listed in the entry for the `DATE_FORMAT()' function.
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2003 6th August 06:22:58 2003'
`GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')'
Returns a format string. This function is useful in combination
with the `DATE_FORMAT()' and the `STR_TO_DATE()' functions. The
three possible values for the first argument and the five possible
values for the second argument result in 15 possible format
strings (for the specifiers used, see the table in the
`DATE_FORMAT()' function description).
*Function Call* *Result*
`GET_FORMAT(DATE,'USA')' `'%m.%d.%Y''
`GET_FORMAT(DATE,'JIS')' `'%Y-%m-%d''
`GET_FORMAT(DATE,'ISO')' `'%Y-%m-%d''
`GET_FORMAT(DATE,'EUR')' `'%d.%m.%Y''
`GET_FORMAT(DATE,'INTERNAL')' `'%Y%m%d''
`GET_FORMAT(DATETIME,'USA')' `'%Y-%m-%d-%H.%i.%s''
`GET_FORMAT(DATETIME,'JIS')' `'%Y-%m-%d %H:%i:%s''
`GET_FORMAT(DATETIME,'ISO')' `'%Y-%m-%d %H:%i:%s''
`GET_FORMAT(DATETIME,'EUR')' `'%Y-%m-%d-%H.%i.%s''
`GET_FORMAT(DATETIME,'INTERNAL')' `'%Y%m%d%H%i%s''
`GET_FORMAT(TIME,'USA')' `'%h:%i:%s %p''
`GET_FORMAT(TIME,'JIS')' `'%H:%i:%s''
`GET_FORMAT(TIME,'ISO')' `'%H:%i:%s''
`GET_FORMAT(TIME,'EUR')' `'%H.%i.%S''
`GET_FORMAT(TIME,'INTERNAL')' `'%H%i%s''
ISO format is ISO 9075, not ISO 8601.
As of MySQL 4.1.4, `TIMESTAMP' can also be used; `GET_FORMAT()'
returns the same values as for `DATETIME'.
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
-> '03.10.2003'
mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
-> 2003-10-31
`GET_FORMAT()' is available as of MySQL 4.1.1. `SET
OPTION' SET OPTION.
`HOUR(TIME)'
Returns the hour for TIME. The range of the return value will be
`0' to `23' for time-of-day values.
mysql> SELECT HOUR('10:05:03');
-> 10
However, the range of `TIME' values actually is much larger, so
`HOUR' can return values greater than `23'.
mysql> SELECT HOUR('272:59:59');
-> 272
`LAST_DAY(DATE)'
Takes a date or datetime value and returns the corresponding value
for the last day of the month. Returns `NULL' if the argument is
invalid.
mysql> SELECT LAST_DAY('2003-02-05');
-> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
-> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
-> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
-> NULL
`LAST_DAY()' is available as of MySQL 4.1.1.
`LOCALTIME'
`LOCALTIME()'
`LOCALTIME' and `LOCALTIME()' are synonyms for `NOW()'. They were
added in MySQL 4.0.6.
`LOCALTIMESTAMP'
`LOCALTIMESTAMP()'
`LOCALTIMESTAMP' and `LOCALTIMESTAMP()' are synonyms for `NOW()'.
They were added in MySQL 4.0.6.
`MAKEDATE(YEAR,DAYOFYEAR)'
Returns a date, given year and day-of-year values. DAYOFYEAR must
be greater than 0 or the result will be `NULL'.
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
-> '2001-01-31', '2001-02-01'
mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
-> '2001-12-31', '2004-12-30'
mysql> SELECT MAKEDATE(2001,0);
-> NULL
`MAKEDATE()' is available as of MySQL 4.1.1.
`MAKETIME(HOUR,MINUTE,SECOND)'
Returns a time value calculated from the HOUR, MINUTE, and SECOND
arguments.
mysql> SELECT MAKETIME(12,15,30);
-> '12:15:30'
`MAKETIME()' is available as of MySQL 4.1.1.
`MICROSECOND(EXPR)'
Returns the microseconds from the time or datetime expression EXPR
as a number in the range from `0' to `999999'.
mysql> SELECT MICROSECOND('12:00:00.123456');
-> 123456
mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');
-> 10
`MICROSECOND()' is available as of MySQL 4.1.1.
`MINUTE(TIME)'
Returns the minute for TIME, in the range `0' to `59'.
mysql> SELECT MINUTE('98-02-03 10:05:03');
-> 5
`MONTH(DATE)'
Returns the month for DATE, in the range `1' to `12'.
mysql> SELECT MONTH('1998-02-03');
-> 2
`MONTHNAME(DATE)'
Returns the full name of the month for DATE.
mysql> SELECT MONTHNAME('1998-02-05');
-> 'February'
`NOW()'
Returns the current date and time as a value in `'YYYY-MM-DD
HH:MM:SS'' or `YYYYMMDDHHMMSS' format, depending on whether the
function is used in a string or numeric context.
mysql> SELECT NOW();
-> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
-> 19971215235026
`PERIOD_ADD(P,N)'
Adds N months to period P (in the format `YYMM' or `YYYYMM').
Returns a value in the format `YYYYMM'. Note that the period
argument P is _not_ a date value.
mysql> SELECT PERIOD_ADD(9801,2);
-> 199803
`PERIOD_DIFF(P1,P2)'
Returns the number of months between periods P1 and P2. P1 and P2
should be in the format `YYMM' or `YYYYMM'. Note that the period
arguments P1 and P2 are _not_ date values.
mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11
`QUARTER(DATE)'
Returns the quarter of the year for DATE, in the range `1' to `4'.
mysql> SELECT QUARTER('98-04-01');
-> 2
`SECOND(TIME)'
Returns the second for TIME, in the range `0' to `59'.
mysql> SELECT SECOND('10:05:03');
-> 3
`SEC_TO_TIME(SECONDS)'
Returns the SECONDS argument, converted to hours, minutes, and
seconds, as a value in `'HH:MM:SS'' or `HHMMSS' format, depending
on whether the function is used in a string or numeric context.
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
`STR_TO_DATE(STR,FORMAT)'
This is the reverse function of the `DATE_FORMAT()' function. It
takes a string STR and a format string FORMAT. `STR_TO_DATE()'
returns a `DATETIME' value if the format string contains both date
and time parts, or a `DATE' or `TIME' value if the string contains
only date or time parts.
The date, time, or datetime values contained in STR should be given
in the format indicated by FORMAT. For the specifiers that can be
used in FORMAT, see the table in the `DATE_FORMAT()' function
description. All other characters are just taken verbatim, thus
not being interpreted. If STR contains an illegal date, time, or
datetime value, `STR_TO_DATE()' returns `NULL'.
mysql> SELECT STR_TO_DATE('03.10.2003 09.20',
-> '%d.%m.%Y %H.%i');
-> '2003-10-03 09:20:00'
mysql> SELECT STR_TO_DATE('10arp', '%carp');
-> '0000-10-00 00:00:00'
mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00',
-> '%Y-%m-%d %H:%i:%s');
-> NULL
Range checking on the parts of date values is as described in
`DATETIME' DATETIME. This means, for example, that a date
with a day part larger than the number of days in a month is
allowable as long as the day part is in the range from 1 to 31.
Also, "zero" dates or dates with part values of 0 are allowed.
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'
`STR_TO_DATE()' is available as of MySQL 4.1.1.
`SUBDATE(DATE,INTERVAL EXPR TYPE)'
`SUBDATE(EXPR,DAYS)'
When invoked with the `INTERVAL' form of the second argument,
`SUBDATE()' is a synonym for `DATE_SUB()'. For information on the
`INTERVAL' argument, see the discussion for `DATE_ADD()'.
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
As of MySQL 4.1.1, the second syntax is allowed, where EXPR is a
date or datetime expression and DAYS is the number of days to be
subtracted from EXPR.
mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
-> '1997-12-02 12:00:00'
`SUBTIME(EXPR,EXPR2)'
`SUBTIME()' subtracts EXPR2 from EXPR and returns the result.
EXPR is a time or datetime expression, and EXPR2 is a time
expression.
mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
-> '1 1:1:1.000002');
-> '1997-12-30 22:58:58.999997'
mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
-> '-00:59:59.999999'
`SUBTIME()' was added in MySQL 4.1.1.
`SYSDATE()'
`SYSDATE()' is a synonym for `NOW()'.
`TIME(EXPR)'
Extracts the time part of the time or datetime expression EXPR.
mysql> SELECT TIME('2003-12-31 01:02:03');
-> '01:02:03'
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
-> '01:02:03.000123'
`TIME()' is available as of MySQL 4.1.1.
`TIMEDIFF(EXPR,EXPR2)'
`TIMEDIFF()' returns the time between the start time EXPR and the
end time EXPR2. EXPR and EXPR2 are time or date-and-time
expressions, but both must be of the same type.
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
-> '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
-> '1997-12-30 01:01:01.000002');
-> '46:58:57.999999'
`TIMEDIFF()' was added in MySQL 4.1.1.
`TIMESTAMP(EXPR)'
`TIMESTAMP(EXPR,EXPR2)'
With one argument, returns the date or datetime expression EXPR as
a datetime value. With two arguments, adds the time expression
EXPR2 to the date or datetime expression EXPR and returns a
datetime value.
mysql> SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
`TIMESTAMP()' is available as of MySQL 4.1.1.
`TIMESTAMPADD(INTERVAL,INT_EXPR,DATETIME_EXPR)'
Adds the integer expression INT_EXPR to the date or datetime
expression DATETIME_EXPR. The unit for INT_EXPR is given by the
`interval' argument, which should be one of the following values:
`FRAC_SECOND', `SECOND', `MINUTE', `HOUR', `DAY', `WEEK', `MONTH',
`QUARTER', or `YEAR'.
The INTERVAL value may be specified using one of keywords as shown,
or with a prefix of `SQL_TSI_'. For example, `DAY' or
`SQL_TSI_DAY' both are legal.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'
`TIMESTAMPADD()' is available as of MySQL 5.0.0.
`TIMESTAMPDIFF(INTERVAL,DATETIME_EXPR1,DATETIME_EXPR2)'
Returns the integer difference between the date or datetime
expressions DATETIME_EXPR1 and DATETIME_EXPR2. The unit for the
result is given by the INTERVAL argument. The legal values for
INTERVAL are the same as those listed in the description of the
`TIMESTAMPADD()' function.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
`TIMESTAMPDIFF()' is available as of MySQL 5.0.0.
`TIME_FORMAT(TIME,FORMAT)'
This is used like the `DATE_FORMAT()' function, but the FORMAT
string may contain only those format specifiers that handle hours,
minutes, and seconds. Other specifiers produce a `NULL' value or
`0'.
If the TIME value contains an hour part that is greater than `23',
the `%H' and `%k' hour format specifiers produce a value larger
than the usual range of `0..23'. The other hour format specifiers
produce the hour value modulo 12.
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'
`TIME_TO_SEC(TIME)'
Returns the TIME argument, converted to seconds.
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
`TO_DAYS(DATE)'
Given a date DATE, returns a daynumber (the number of days since
year 0).
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('1997-10-07');
-> 729669
`TO_DAYS()' is not intended for use with values that precede the
advent of the Gregorian calendar (1582), because it does not take
into account the days that were lost when the calendar was changed.
Remember that MySQL converts two-digit year values in dates to
four-digit form using the rules in Date and time types.
For example, `'1997-10-07'' and `'97-10-07'' are seen as identical
dates:
mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');
-> 729669, 729669
For other dates before 1582, results from this function are
undefined.
`UNIX_TIMESTAMP()'
`UNIX_TIMESTAMP(DATE)'
If called with no argument, returns a Unix timestamp (seconds since
`'1970-01-01 00:00:00'' GMT) as an unsigned integer. If
`UNIX_TIMESTAMP()' is called with a DATE argument, it returns the
value of the argument as seconds since `'1970-01-01 00:00:00''
GMT. DATE may be a `DATE' string, a `DATETIME' string, a
`TIMESTAMP', or a number in the format `YYMMDD' or `YYYYMMDD' in
local time.
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When `UNIX_TIMESTAMP' is used on a `TIMESTAMP' column, the function
returns the internal timestamp value directly, with no implicit
"string-to-Unix-timestamp" conversion. If you pass an
out-of-range date to `UNIX_TIMESTAMP()', it returns `0', but
please note that only basic range checking is performed (year from
`1970' to `2037', month from `01' to `12', day from `01' from
`31').
If you want to subtract `UNIX_TIMESTAMP()' columns, you might want
to cast the result to signed integers. Cast Functions.
`UTC_DATE'
`UTC_DATE()'
Returns the current UTC date as a value in `'YYYY-MM-DD'' or
`YYYYMMDD' format, depending on whether the function is used in a
string or numeric context.
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
-> '2003-08-14', 20030814
`UTC_DATE()' is available as of MySQL 4.1.1.
`UTC_TIME'
`UTC_TIME()'
Returns the current UTC time as a value in `'HH:MM:SS'' or `HHMMSS'
format, depending on whether the function is used in a string or
numeric context.
mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
-> '18:07:53', 180753
`UTC_TIME()' is available as of MySQL 4.1.1.
`UTC_TIMESTAMP'
`UTC_TIMESTAMP()'
Returns the current UTC date and time as a value in `'YYYY-MM-DD
HH:MM:SS'' or `YYYYMMDDHHMMSS' format, depending on whether the
function is used in a string or numeric context.
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
-> '2003-08-14 18:08:04', 20030814180804
`UTC_TIMESTAMP()' is available as of MySQL 4.1.1.
`WEEK(DATE[,MODE])'
The function returns the week number for DATE. The two-argument
form of `WEEK()' allows you to specify whether the week starts on
Sunday or Monday and whether the return value should be in the
range from `0' to `53' or from `1' to `53'. If the MODE argument is
omitted, the value of the `default_week_format' system variable is
used (or `0' before MySQL 4.0.14). Server system
variables.
The following table describes how the MODE argument works:
*First day*
*Mode* *of week* *Range* *Week 1 is the first week...*
0 Sunday 0-53 with a Sunday in this year
1 Monday 0-53 with more than 3 days this year
2 Sunday 1-53 with a Sunday in this year
3 Monday 1-53 with more than 3 days this year
4 Sunday 0-53 with more than 3 days this year
5 Monday 0-53 with a Monday in this year
6 Sunday 1-53 with more than 3 days this year
7 Monday 1-53 with a Monday in this year
A MODE value of `3' can be used as of MySQL 4.0.5. Values of `4'
and above can be used as of MySQL 4.0.17.
mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53
Note: In MySQL 4.0, `WEEK(DATE,0)' was changed to match the
calendar in the USA. Before that, `WEEK()' was calculated
incorrectly for dates in the USA. (In effect, `WEEK(DATE)' and
`WEEK(DATE,0)' were incorrect for all cases.)
Note that if a date falls in the last week of the previous year,
MySQL returns `0' if you don't use `2', `3', `6', or `7' as the
optional `mode' argument:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
One might argue that MySQL should return `52' for the `WEEK()'
function, because the given date actually occurs in the 52nd week
of 1999. We decided to return `0' instead because we want the
function to return "the week number in the given year." This
makes use of the `WEEK()' function reliable when combined with
other functions that extract a date part from a date.
If you would prefer the result to be evaluated with respect to the
year that contains the first day of the week for the given date,
you should use `0', `2', `5', or `7' as the optional `mode'
argument.
mysql> SELECT WEEK('2000-01-01',2);
-> 52
Alternatively, use the `YEARWEEK()' function:
mysql> SELECT YEARWEEK('2000-01-01');
-> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
-> '52'
`WEEKDAY(DATE)'
Returns the weekday index for DATE (`0' = Monday, `1' = Tuesday,
... `6' = Sunday).
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
-> 1
mysql> SELECT WEEKDAY('1997-11-05');
-> 2
`WEEKOFYEAR(DATE)'
Returns the calendar week of the date as a number in the range
from `1' to `53'. It is a compatibility function that is
equivalent to `WEEK(DATE,3)'.
mysql> SELECT WEEKOFYEAR('1998-02-20');
-> 8
`WEEKOFYEAR()' is available as of MySQL 4.1.1.
`YEAR(DATE)'
Returns the year for DATE, in the range `1000' to `9999'.
mysql> SELECT YEAR('98-02-03');
-> 1998
`YEARWEEK(DATE)'
`YEARWEEK(DATE,START)'
Returns year and week for a date. The START argument works exactly
like the START argument to `WEEK()'. The year in the result may be
different from the year in the date argument for the first and the
last week of the year.
mysql> SELECT YEARWEEK('1987-01-01');
-> 198653
Note that the week number is different from what the `WEEK()'
function would return (`0') for optional arguments `0' or `1', as
`WEEK()' then returns the week in the context of the given year.
`YEARWEEK()' was added in MySQL 3.23.8.
Info Catalog
(mysql.info.gz) Numeric Functions
(mysql.info.gz) Functions
(mysql.info.gz) Fulltext Search
automatically generated byinfo2html