MySQL date and time functions, Part 2

• ADDDATE(datetime, INTERVAL expression datetimetype)
  A synonym for DATE_ADD()
• CURDATE()
  A synonym for CURRENT_DATE()
• CURRENT_DATE()
  Returns the current system date as YYYY-MM-DD (or YYYYMMDD if the context requires this, such as when you add a numeric to the result)

  mysql> SELECT CURRENT_DATE();
  +----------------+
  | CURRENT_DATE() |
  +----------------+
  | 2003-04-14     |
  +----------------+
  

• CURRENT_TIME()
  Returns the current system time as hh:mm:ss (or hhmmss if the context requires this, such as when you add a numeric to the result)
• CURRENT_TIMESTAMP()
  A synonym for NOW()

  mysql> SELECT CURRENT_TIME();
  +----------------+
  | CURRENT_TIME() |
  +----------------+
  | 12:03:10       |
  +----------------+
  

• CURTIME()
  A synonym for CURRENT_TIME()
• DATE_ADD(datetime, INTERVAL expression datetimetype)
  Adds the expression to the datetime supplied. The expression can be any valid datetimetype.

  mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
  +-----------------------------------------+
  | DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
  +-----------------------------------------+
  | 2003-07-27                              |
  +-----------------------------------------+
  

• DATE_FORMAT(datetime, format_string)
  Formats the datetime according to the format string

  mysql> SELECT DATE_FORMAT('2003-07-14','%b %d,%Y');
  +--------------------------------------+
  | DATE_FORMAT('2003-07-14','%b %d,%Y') |
  +--------------------------------------+
  | Jul 14,2003                          |
  +--------------------------------------+
  

• DATE_SUB(datetime,INTERVAL expression datetimetype)
  The same as DATE_ADD, but subtracting instead of adding

  mysql> SELECT DATE_SUB('2003-07-14', INTERVAL 14 DAY);
  +-----------------------------------------+
  | DATE_SUB('2003-07-14', INTERVAL 14 DAY) |
  +-----------------------------------------+
  | 2003-06-30                              |
  +-----------------------------------------+
  

• DAYNAME(datetime)
  Returns the full name of the day for the specified date.

  mysql> SELECT DAYNAME('2003-07-14');
  +-----------------------+
  | DAYNAME('2003-07-14') |
  +-----------------------+
  | Monday                |
  +-----------------------+
  

• DAYOFMONTH(datetime)
  Returns a numeric (from 1-31) for the day of the month

  mysql> SELECT DAYOFMONTH('2003-07-14');
  +--------------------------+
  | DAYOFMONTH('2003-07-14') |
  +--------------------------+
  |                       14 |
  +--------------------------+
  

• DAYOFWEEK(datetime)
  Returns a numeric corresponding to the day of the week (from 1 for Sunday to 7 for Saturday)

  mysql> SELECT DAYOFWEEK('2003-07-14');
  +-------------------------+
  | DAYOFWEEK('2003-07-14') |
  +-------------------------+
  |                       2 |
  +-------------------------+
  

• DAYOFYEAR(datetime)
  Returns a numeric (from 1-366) corresponding to the day of the year

  mysql> SELECT DAYOFYEAR('2003-07-14');
  +-------------------------+
  | DAYOFYEAR('2003-07-14') |
  +-------------------------+
  |                     195 |
  +-------------------------+
  

• EXTRACT(datetimetype FROM datetime)
  Returns the portion of the datetime corresponding to the supplied datetimetype

  mysql> SELECT EXTRACT(HOUR_MINUTE FROM '2003-07-14 12:32:14');
  +-------------------------------------------------+
  | EXTRACT(HOUR_MINUTE FROM '2003-07-14 12:32:14') |
  +-------------------------------------------------+
  |                                            1232 |
  +-------------------------------------------------+
  

• FROM_DAYS(number_of_days)
  Converts the supplied numeric into a date based upon the number of days since 1 Jan of the year 0 (without taking days lost in the change to the Gregorian calendar into account)

  mysql> SELECT FROM_DAYS(731682);
  +-------------------+
  | FROM_DAYS(731682) |
  +-------------------+
  | 2003-04-12        |
  +-------------------+
  

• FROM_UNIXTIME(unix_timestamp [,format_string])
  Converts a Unix timestamp into a date, with the optional format string determining how it is specified

  mysql> SELECT FROM_UNIXTIME(1064431682);
  +---------------------------+
  | FROM_UNIXTIME(1064431682) |
  +---------------------------+
  | 2003-09-24 21:28:02       |
  +---------------------------+
  

• HOUR(time)
  Returns a numeric (from 0-23) for the hour of the specified time

  mysql> SELECT HOUR('12:32:15');
  +------------------+
  | HOUR('12:32:15') |
  +------------------+
  |               12 |
  +------------------+
  

• MINUTE(time)
  Returns a numeric (from 0-59) for the minute of the specified time

  mysql> SELECT MINUTE('12:32:12');
  +--------------------+
  | MINUTE('12:32:12') |
  +--------------------+
  |                 32 |
  +--------------------+
  

• MONTH(datetime)
  Returns a numeric (from 1-12) for the month of the specified date

  mysql> SELECT MONTH('2003-07-12');
  +---------------------+
  | MONTH('2003-07-12') |
  +---------------------+
  |                   7 |
  +---------------------+
  

• MONTHNAME(datetime)
  Returns the full name of the month for the specified date.

  mysql> SELECT MONTHNAME('2003-07-12');
  +-------------------------+
  | MONTHNAME('2003-07-12') |
  +-------------------------+
  | July                    |
  +-------------------------+
  

• NOW()
  Returns the current timestamp.

  mysql> SELECT NOW();
  +---------------------+
  | NOW()               |
  +---------------------+
  | 2003-04-14 12:14:45 |
  +---------------------+
  

• PERIOD_ADD(period, months)
  Adds the months to the period (YYMM or YYYYMM) returning the result as YYYYMM

  mysql> SELECT PERIOD_ADD(200307,6);
  +----------------------+
  | PERIOD_ADD(200307,6) |
  +----------------------+
  |               200401 |
  +----------------------+
  

• PERIOD_DIFF(period, period)
  Returns the number of months between the two periods (which are specified as YYMM or YYYYMM)

  mysql> SELECT PERIOD_DIFF(200307,200209);
  +----------------------------+
  | PERIOD_DIFF(200307,200209) |
  +----------------------------+
  |                         10 |
  +----------------------------+
  

• QUARTER(datetime)
  Returns a numeric (from 1-4) for the quarter of the specified date

  mysql> SELECT QUARTER('2003-07-12');
  +-----------------------+
  | QUARTER('2003-07-12') |
  +-----------------------+
  |                     3 |
  +-----------------------+
  

• SEC_TO_TIME(seconds)
  Converts the seconds to a time

  mysql> SELECT SEC_TO_TIME(2349);
  +-------------------+
  | SEC_TO_TIME(2349) |
  +-------------------+
  | 00:39:09          |
  +-------------------+
  

• SECOND(time)
  Returns the seconds for a specified time

  mysql> SELECT SECOND('12:32:11');
  +--------------------+
  | SECOND('12:32:11') |
  +--------------------+
  |                 11 |
  +--------------------+
  

• SUBDATE(datetime, INTERVAL expression datetimetype)
  A synonym for DATE_SUB()
• SYSDATE()
  A synonym for NOW()
• TIME_FORMAT(time, format_string)
  A subset of DATE_FORMAT dealing only with times

  mysql> SELECT TIME_FORMAT('2003-07-14 11:23:19','%r');
  +-----------------------------------------+
  | TIME_FORMAT('2003-07-14 11:23:19','%r') |
  +-----------------------------------------+
  | 11:23:19 AM                             |
  +-----------------------------------------+
  

• TIME_TO_SEC(time)
  Returns the time converted to seconds

  mysql> SELECT TIME_TO_SEC('11:23:19');
  +-------------------------+
  | TIME_TO_SEC('11:23:19') |
  +-------------------------+
  |                   40999 |
  +-------------------------+
  

• TO_DAYS(datetime)
  Returns the number of days passed since 1 Jan the year 0 for the specified date (not taking into account Gregorian calendar confusions)

  mysql> SELECT TO_DAYS('2003-07-12');
  +-----------------------+
  | TO_DAYS('2003-07-12') |
  +-----------------------+
  |                731773 |
  +-----------------------+
  

• UNIX_TIMESTAMP([datetime])
  Returns a Unix timestamp for the current datetime, or the one specified

  mysql> SELECT UNIX_TIMESTAMP();
  +------------------+
  | UNIX_TIMESTAMP() |
  +------------------+
  |       1050315703 |
  +------------------+
  

• WEEK(datetime [,week_start])
  Returns a numeric (from 0-53) for the week of the supplied datetime. Weeks start on Sunday unless the optional week_start argument is set to 1, in which case weeks are assumed to start on Monday

  mysql> SELECT WEEK('2003-07-12');
  +--------------------+
  | WEEK('2003-07-12') |
  +--------------------+
  |                 27 |
  +--------------------+
  

• WEEKDAY(datetime)
  Returns the day of the week for the supplied date, from 0 for Monday to 6 for Sunday

  mysql> SELECT WEEKDAY('2003-07-12');
  +-----------------------+
  | WEEKDAY('2003-07-12') |
  +-----------------------+
  |                     5 |
  +-----------------------+
  

• YEAR(datetime)
  Returns the year of the specified date

  mysql> SELECT YEAR('2003-07-12');
  +--------------------+
  | YEAR('2003-07-12') |
  +--------------------+
  |               2003 |
  +--------------------+
  

• YEARWEEK(datetime [,week_start])
  Returns a combination of year and week for the specified date. The week_start argument works the same as for the WEEK() function.

  mysql> SELECT YEARWEEK('2003-07-12');
  +------------------------+
  | YEARWEEK('2003-07-12') |
  +------------------------+
  |                 200327 |
  +------------------------+
  

» See All Articles by Columnist Ian Gilfillan