Calculating, Formatting, and Transformation Functions
ADDDATE(d, n) Adds n days to the starting time d.
ADDDATE(...) Adds a time interval to the starting time d (see below). Synonym: DATE_ADD()
ADDTIME(d, t) Adds the time t (TIME) to the starting time d (DATETIME).
CONVERT_TZ(d, tz1, tz2) Converts the time d from the time zone tz1 into the time zone tz2.
DATE(d) Returns only the date portion of a DATETIME expression.
DATEDIFF(d1, d2) Returns the number of days between d1 and d2.
DATE_FORMAT(d, form) Formats d according to formatting string f.
DAYNAME(date) Returns 'Monday', 'Tuesday', etc.
DAYOFMONTH(date) Returns the day of the month (1 to 31).
DAYOFWEEK(date) Returns the day of the week (1 = Sunday through 7 = Saturday).
DAYOFYEAR(date) Returns the day in the year (1 to 366).
EXTRACT(i FROM date) Returns a number for the desired interval. EXTRACT(YEAR FROM '2003-12-31') Returns 2003.
FROM_DAYS(n) Returns the date n days after the year 0.
FROM_DAYS(3660) Returns '0010-01-08'.
FROM_UNIXTIME(t) Transforms the Unix timestamp number t into a date. FROM_UNIXTIME(0) Returns '1970-01-01 01:00:00'.
FROM_UNIXTIME(t, f ) As above, but with formatting as in DATE_FORMAT.
GET_FORMAT(...) Returns predefined formatting code for DATE_FORMAT
HOUR(time) Returns the hour (0 to 23).
LAST_DAY(d) Returns the last day of the month specified by the date d. LAST_DAY('2005-02-01') returns '2005-02-28'.
MAKEDATE(y, dayofyear) Creates a DATE expression from the input of year and day.
MAKETIME(h, m, s) Creates a TIME expression from the input for hours, minutes, and seconds.
MICROSECOND(d) Returns the number of microseconds (0 to 999999).
MINUTE(time) Returns the minute (0 to 59).
MONTH(date) Returns the month (1 to 12).
MONTHNAME(date) Returns the name of the month ('January', etc.).
PERIOD_ADD(s, n) Adds n months to the start date, which must be specified in the form 'YYYYMM'.
PERIOD_DIFF(s, e) Returns the number of months between the start and end dates. Both times must be given in the form 'YYYYMM'.
QUARTER(date) Returns the quarter (1 to 4).
SECOND(time) Returns the second (0 to 59).
SEC_TO_TIME(n) Returns the time n seconds after midnight. SEC_TO_TIME(3603) Returns '01:00:03'.
STR_TO_DATE(s, form) Interprets the string s according to the formatting code in form. STR_TO_DATE is the inverse function of DATE_FORMAT.
SUBDATE(d, n) Subtracts n days from the starting time d.
SUBDATE(d, ) Subtracts a time interval from the starting time d (see below). Synonym: DATE_ADD()
SUBTIME(d, t) Subtracts the time t (TIME) from the starting time d (DATETIME).
TIMESTAMP(s) Returns the starting time given in the string as a TIMESTAMP value. TIMESTAMP('2005-12-31') returns '2005-12-31 00:00:00'.
TIMESTAMP(s, time) Returns s + time as a TIMESTAMP value.
TIMESTAMPADD(i, n, s) Adds n times the interval i (e.g., MONTH) to the starting time s.
TIMESTAMPDIFF(i, s, e) Returns the number of intervals i between the start time s and the end time e. TIMESTAMPDIFF(HOUR,'2005-12-31', '2006-01-01') returns 24.
TIME_FORMAT(time, f ) Like DATE_FORMAT, but for times only. TIME_TO_SEC(time) returns the number of seconds since midnight.
TO_DAYS(date) Returns the number of days since the year 0.
UNIX_TIMESTAMP(d) Returns the timestamp number for the given date.
WEEK(date) Returns the week number (1 for the week beginning with the first Sunday in the year).
WEEK(date, mode) Returns the week number (0 to 53 or 1 to 53). The parameter mode determines the first day of the week and how a week is defined.
WEEKDAY(date) Returns the day of the week (0 = Monday, 1 = Tuesday, etc.).
WEEKOFYEAR(date) Returns the calendar week (1 to 53).
YEAR(date) Returns the year.
YEARWEEK(date, mode) Returns an integer or string, depending on context, that consists of the year number and week number. mode is set as in WEEK.
Related examples in the same category