MySQL date and time functions, Part 2 - Page 3

April 15, 2003

Date functions reference

  • 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers