DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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