dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Apr 15, 2003

MySQL date and time functions, Part 2 - Page 3

By Ian Gilfillan

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.