(mysql.info) date-and-time-functions
Info Catalog
(mysql.info) numeric-functions
(mysql.info) functions
(mysql.info) mysql-calendar
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 rows 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 selects rows with dates that lie in the future.
Functions that expect date values usually accept datetime values and
ignore the time part. Functions that expect time values usually 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
always produce the same result (for our purposes a single query also
includes a call to a stored routine or trigger and all sub-routines
called by that routine/trigger). This principle also applies to
`CURDATE()', `CURTIME()', `UTC_DATE()', `UTC_TIME()',
`UTC_TIMESTAMP()', and to any of their synonyms.
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. In addition, `UNIX_TIMESTAMP()' assumes that its argument is a
datetime value in the current time zone. See time-zone-support.
Some date functions can be used with `zero' dates or incomplete dates
such as `'2001-11-00'', whereas others cannot. Functions that extract
parts of dates typically work with incomplete dates. For example:
mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
-> 0, 0
Other functions expect complete dates and return `NULL' for incomplete
dates. These include functions that perform date arithmetic or that map
parts of dates to names. For example:
mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
-> NULL
mysql> SELECT DAYNAME('2006-05-00');
-> NULL
* `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'
When invoked with the DAYS form of the second argument, MySQL
treats it as an integer 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'
* `CONVERT_TZ(DT,FROM_TZ,TO_TZ)'
`CONVERT_TZ()' converts a datetime value DT from the time zone
given by FROM_TZ to the time zone given by TO_TZ and returns the
resulting value. Time zones are 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 in
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','+10:00');
-> '2004-01-01 22: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.
* `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'
* `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
* `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 the expected form of the EXPR argument
for each TYPE value.
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 values `QUARTER' and `WEEK' are available beginning with 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.
Date arithmetic also can be performed using `INTERVAL' together
with the `+' or `-' operator:
`date' + INTERVAL EXPR TYPE
`date' - INTERVAL EXPR TYPE
`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.
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 a 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 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 arithmetic operations require complete dates and do not work
with incomplete dates such as `'2005-07-00'' or badly malformed
dates:
mysql> SELECT DATE_ADD('2006-07-00', INTERVAL 1 DAY);
-> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
-> NULL
* `DATE_FORMAT(DATE,FORMAT)'
Formats the DATE value according to the FORMAT string.
The following specifiers may be used in the FORMAT string. The
‘`%'’ character is required before format specifier characters.
*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 ‘`%'’ character
`%X' X, for any ‘X’ not listed above
Ranges for the month and day specifiers begin with zero due to the
fact that MySQL allows the storing of incomplete dates such as
`'2004-00-00''.
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'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
* `DAY(DATE)'
`DAY()' is a synonym for `DAYOFMONTH()'.
* `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 `0' 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
* `FROM_DAYS(N)'
Given a day number N, returns a `DATE' value.
mysql> SELECT FROM_DAYS(729669);
-> '1997-10-07'
Use `FROM_DAYS()' with caution on old dates. It is not intended
for use with values that precede the advent of the Gregorian
calendar (1582). See mysql-calendar.
* `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.
UNIX_TIMESTAMP is an internal timestamp value such as is produced
by the `UNIX_TIMESTAMP()' function.
If FORMAT is given, the result is formatted according to the FORMAT
string, which is used the same way as listed in the entry for the
`DATE_FORMAT()' function.
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2003 6th August 06:22:58 2003'
Note: If you use `UNIX_TIMESTAMP()' and `FROM_UNIXTIME()' to
convert between `TIMESTAMP' values and Unix timestamp values, the
conversion is lossy because the mapping is not one-to-one in both
directions. For details, see the description of the
`UNIX_TIMESTAMP()' function.
* `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 possible values for the first and second arguments result in
several possible format strings (for the specifiers used, see the
table in the `DATE_FORMAT()' function description). ISO format
refers to ISO 9075, not ISO 8601.
*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''
`TIMESTAMP' can also be used as the first argument to
`GET_FORMAT()', in which case the function 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'
* `HOUR(TIME)'
Returns the hour for TIME. The range of the return value is `0' to
`23' for time-of-day values. However, the range of `TIME' values
actually is much larger, so `HOUR' can return values greater than
`23'.
mysql> SELECT HOUR('10:05:03');
-> 10
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
* `LOCALTIME', `LOCALTIME()'
`LOCALTIME' and `LOCALTIME()' are synonyms for `NOW()'.
* `LOCALTIMESTAMP', `LOCALTIMESTAMP()'
`LOCALTIMESTAMP' and `LOCALTIMESTAMP()' are synonyms for `NOW()'.
* `MAKEDATE(YEAR,DAYOFYEAR)'
Returns a date, given year and day-of-year values. DAYOFYEAR must
be greater than 0 or the result is `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
* `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'
* `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
* `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 `0' 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
Within a stored routine or trigger, `NOW()' returns a constant
time that indicates the time at which the routine or triggering
statement began to execute. This differs from the behavior for
`SYSDATE()', which returns the exact time at which it executes.
* `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 inverse 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 `DATE_FORMAT()' function
description. If STR contains an illegal date, time, or datetime
value, `STR_TO_DATE()' returns `NULL'. Starting from MySQL 5.0.3,
an illegal value also produces a warning.
Range checking on the parts of date values is as described in
datetime. This means, for example, that `zero' dates or
dates with part values of 0 are allowed unless the SQL mode is set
to disallow such values.
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'
* `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'
The second form allows the use of an integer value for DAYS. In
such cases, it is interpreted as the number of days to be
subtracted from the date or datetime expression EXPR.
mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
-> '1997-12-02 12:00:00'
string to a date because the combination of a year and week does
not uniquely identify a year and month if the week crosses a month
boundary. To convert a year-week to a date, then you should also
specify the weekday:
mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
-> '2004-10-18'
* `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'
* `SYSDATE()'
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.
Within a stored routine or trigger, `SYSDATE()' returns the time
at which it executes. This differs from the behavior for `NOW()',
which returns the the time at which the routine or triggering
statement began to execute.
* `TIME(EXPR)'
Extracts the time part of the time or datetime expression EXPR and
returns it as a string.
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'
* `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'
* `TIMESTAMP(EXPR)', `TIMESTAMP(EXPR,EXPR2)'
With a single argument, this function returns the date or datetime
expression EXPR as a datetime value. With two arguments, it adds
the time expression EXPR2 to the date or datetime expression EXPR
and returns the result as 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'
* `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' and
`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 format specifiers only for 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 day number (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. For dates before 1582 (and possibly a later year in other
locales), results from this function are not reliable. See
mysql-calendar, for details.
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
* `UNIX_TIMESTAMP()', `UNIX_TIMESTAMP(DATE)'
If called with no argument, returns a Unix timestamp (seconds
since `'1970-01-01 00:00:00'' UTC) 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'' UTC.
DATE may be a `DATE' string, a `DATETIME' string, a `TIMESTAMP',
or a number in the format `YYMMDD' or `YYYYMMDD'. The server
interprets DATE as a value in the current time zone and converts
it to an internal value in UTC. Clients can set their time zone as
described in time-zone-support.
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'.
Note: If you use `UNIX_TIMESTAMP()' and `FROM_UNIXTIME()' to
convert between `TIMESTAMP' values and Unix timestamp values, the
conversion is lossy because the mapping is not one-to-one in both
directions. For example, due to conventions for local time zone
changes, it is possible for two `UNIX_TIMESTAMP()' to map two
`TIMESTAMP' values to the same Unix timestamp value.
`FROM_UNIXTIME()' will map that value back to only one of the
original `TIMESTAMP' values. Here is an example, using `TIMESTAMP'
values in the `CET' time zone:
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+
If you want to subtract `UNIX_TIMESTAMP()' columns, you might want
to cast the result to signed integers. See 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_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_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
* `WEEK(DATE[,MODE])'
This 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. See 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
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 that if a date falls in the last week of the previous year,
MySQL returns `0' if you do not 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,
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'. `WEEKOFYEAR()' is a compatibility function that
is equivalent to `WEEK(DATE,3)'.
mysql> SELECT WEEKOFYEAR('1998-02-20');
-> 8
* `YEAR(DATE)'
Returns the year for DATE, in the range `1000' to `9999', or `0'
for the `zero' date.
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.
Info Catalog
(mysql.info) numeric-functions
(mysql.info) functions
(mysql.info) mysql-calendar
automatically generated byinfo2html