RSS Twitter
ZendCon 2012 Partner
 
Articles Databases

MySQL Date and Time Functions, Part 2 - Page 2

by: Ian Gilfillan
|
February 22, 2007

  • 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 |
+------------------------+


    This article originally appeared on DatabaseJournal.com.



    « Previous Page
    1
    |
    2

    Comment and Contribute
    0 Comments (click to add your comment)

    Your comment has been submitted and is pending approval.

    Author:
    Ian Gilfillan

    Comment:



    Comment:

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