MySQL Date and Time Functions, Part 2 Page 2

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.