Name Description

Add time values (intervals) to a date value

ADDDATE(date,INTERVAL expr unit), ADDDATE(ex
pr,days)

SELECT ADDDATE('2008-01-02', 31);

ADDDATE() SELECT ADDDATE('2008-01-02', interval 31 day);

Add time

ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the

result. expr1 is a time or datetime expression,
and expr2 is a time expression.

mysql> SELECT ADDTIME('2007-12-31

23:59:59.999999', '1 1:1:1.000002');

-> '2008-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999',

'02:00:00.999998');

ADDTIME() -> '03:00:01.999997'

Convert from one time zone to another

CONVERT_TZ(dt,from_tz,to_tz)

SELECT CONVERT_TZ('2004-01-01
12:00:00','GMT','MET');

SELECT CONVERT_TZ('2004-01-01
12:00:00','+00:00','+10:00');

CONVERT_TZ()

CURDATE() Return the current date

 Name Description

SELECT CURDATE();
-> '2008-06-13'

Synonyms for CURDATE()

CURRENT_DATE()
CURRENT_DATE

Synonyms for CURTIME()

CURRENT_TIME() SELECT CURTIME();
CURRENT_TIME -> '23:50:26'

Synonyms for NOW()

SELECT NOW();
CURRENT_TIMESTA -> '2007-12-15 23:50:26'
MP()
CURRENT_TIMESTA
MP

Return the current time

CURTIME()

Extract the date part of a date or datetime

expression

SELECT DATE('2003-12-31 01:02:03');

DATE() -> '2003-12-31'

DATE_ADD() Add time values (intervals) to a date value

DATE_ADD(date,INTERVAL expr unit), 

DATE_SUB(date,INTERVAL expr unit)

The date argument specifies the starting date or

datetime value. expr is an expression specifying the
interval value to be added from the starting date
Name Description

SELECT DATE_ADD('2100-12-31 23:59:59',

-> INTERVAL '1:1' MINUTE_SECOND);

-> '2101-01-01 00:01:00'

SELECT DATE_ADD('2010-12-31 23:59:59',

-> INTERVAL 1 DAY);
-> '2011-01-01 23:59:59'

SELECT DATE_ADD('1900-01-01 00:00:00',

-> INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'

unit Value Expected expr Format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROS
ECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROS 'MINUTES:SECONDS.MICROSECO
ECOND NDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSEC 'HOURS:MINUTES:SECONDS.MIC
OND ROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
'DAYS
DAY_MICROSECO HOURS:MINUTES:SECONDS.MIC
ND ROSECONDS'
DAY_SECOND 'DAYS
 Name Description

HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'

DATE_FORMAT() Format date as specified

DATE_FORMAT(date,format)

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W

%M %Y');
-> 'Sunday October 2009'

SELECT DATE_FORMAT('1900-10-04 22:23:00',

-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
Name Description

SELECT DATE_FORMAT('1997-10-04 22:23:00',

-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'

%a Abbreviated weekday name (Sun..Sat)

%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
Day of the month with English suffix
%D (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
Time, 12-hour (hh:mm:ss followed
%r by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
Week (00..53), where Sunday is the first day of
%U the week; WEEK()mode 0
Week (00..53), where Monday is the first day
%u of the week; WEEK()mode 1
%V Week (01..53), where Sunday is the first day of
 Name Description

the week; WEEK()mode 2; used with %X

Week (01..53), where Monday is the first day
%v of the week; WEEK()mode 3; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
Year for the week where Sunday is the first day
of the week, numeric, four digits; used
%X with %V
Year for the week, where Monday is the first
day of the week, numeric, four digits; used
%x with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
Subtract a time value (interval) from a date

DATE_SUB() Similar to date_add()

Subtract two dates

DATEDIFF(expr1,expr2)

returns expr1 − expr2 expressed as a value in days

from one date to the other. expr1 and expr2 are
date or date-and-time expressions. Only the date
parts of the values are used in the calculation.

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-

30');
-> 1

SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-

31');
DATEDIFF() -> -31

Synonym for DAYOFMONTH()

DAY()
 Name Description

Return the name of the weekday

DAYNAME(date)

SELECT DAYNAME('2007-02-03');
DAYNAME() -> 'Saturday'

Return the day of the month (0-31)

DAYOFMONTH(date)

SELECT DAYOFMONTH('2007-02-03');
DAYOFMONTH() -> 3

Return the weekday index of the argument

Returns the weekday index for date (1 =

Sunday, 2 = Monday, …, 7 = Saturday). 

SELECT DAYOFWEEK('2007-02-03');
DAYOFWEEK() -> 7

Return the day of the year (1-366)

DAYOFYEAR(date)

SELECT DAYOFYEAR('2007-02-03');
DAYOFYEAR() -> 34

EXTRACT() Extract part of a date

EXTRACT(unit FROM date)

The EXTRACT() function uses the same kinds of unit

specifiers as DATE_ADD()

SELECT EXTRACT(YEAR FROM '2009-07-02');

 Name Description

-> 2009

SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02

01:02:03');
-> 200907

SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02

01:02:03');
-> 20102

Convert a day number to a date

FROM_DAYS(N)

Given a day number N, returns a DATE value.

SELECT FROM_DAYS(730669);
-> '2007-07-03'

FROM_DAYS()

Format Unix timestamp as a date

FROM_UNIXTIME(unix_timestamp),

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of
the unix_timestamp argument as a value in 'YYYY-
MM-DD
HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format

SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
FROM_UNIXTIME() -> '2015 13th November 10:08:01 2015'
 Name Description

Return a date format string

GET_FORMAT({DATE|TIME|DATETIME},
{'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

The possible values for the first and second

arguments result in several possible format strings
(for the specifiers used, see the table

Function Call Result

GET_FORMAT(DATE,'USA'
) '%m.%d.%Y'
GET_FORMAT(DATE,'JIS') '%Y-%m-%d'
GET_FORMAT(DATE,'ISO'
) '%Y-%m-%d'
GET_FORMAT(DATE,'EUR'
) '%d.%m.%Y'
GET_FORMAT(DATE,'INTE
RNAL') '%Y%m%d'
GET_FORMAT(DATETIME,
'USA') '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,
'JIS') '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,
'ISO') '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,
'EUR') '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,
'INTERNAL') '%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA'
) '%h:%i:%s %p'
GET_FORMAT(TIME,'JIS') '%H:%i:%s'
GET_FORMAT(TIME,'ISO') '%H:%i:%s'
GET_FORMAT(TIME,'EUR'
) '%H.%i.%s'
GET_FORMAT() GET_FORMAT(TIME,'INTE '%H%i%s'
 Name Description

RNAL')
Extract the hour

HOUR(time)

SELECT HOUR('10:05:03');
-> 10

SELECT HOUR('272:59:59');
-> 272

Returns the hour for time. The range of the return

value is 0 to 23 for time-of-day values. However, the
range of TIME values actually is much larger,
HOUR() so HOURcan return values greater than 23

Return the last day of the month for the argument

LAST_DAY(date)

SELECT LAST_DAY('2003-02-05');
-> '2003-02-28'

SELECT LAST_DAY('2004-02-05');
-> '2004-02-29'

SELECT LAST_DAY('2003-03-32');
LAST_DAY -> NULL

LOCALTIME()
LOCALTIME Synonym for NOW()

LOCALTIMESTAMP
LOCALTIMESTAMP() Synonym for NOW()

MAKEDATE() Create a date from the year and day of year

MAKEDATE(year,dayofyear)

SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);

 Name Description

-> '2011-01-31', '2011-02-01'

SELECT MAKEDATE(2011,365),
MAKEDATE(2014,365);
-> '2011-12-31', '2014-12-31'

Create time from hour, minute, second

MAKETIME(hour,minute,second)

SELECT MAKETIME(12,15,30);
MAKETIME() -> '12:15:30'

Return the microseconds from argument

SELECT MICROSECOND('12:00:00.123456');
-> 123456

SELECT MICROSECOND('2009-12-31 23:59:59');

MICROSECOND() -> 10

Return the minute from the argument

MINUTE(time)
Returns the minute for time, in the range 0 to 59.

SELECT MINUTE('2008-02-03 10:05:03');

MINUTE() -> 5

Return the month from the date passed

MONTH(date)
Returns the month for date, in the range 1 to 12 for
January to December, or 0 for dates such as '0000-
00-00' or '2008-00-00' that have a zero month part.

SELECT MONTH('2008-02-03');
-> 2

MONTH()
 Name Description

Return the name of the month

MONTHNAME(date)

SELECT MONTHNAME('2008-02-03');
-> 'February'

MONTHNAME()

NOW() Return the current date and time

Add a period to a year-month

PERIOD_ADD(P,N)
Adds N months to period P (in the
format YYMM or YYYYMM). Returns a value in the
format YYYYMM
Note that the period argument P is not a date value

SELECT PERIOD_ADD(200801,2);
-> 200803

PERIOD_ADD()

Return the number of months between periods

PERIOD_DIFF(P1,P2)

Returns the number of months between

periods P1 and P2. P1 and P2 should be in the
format YYMM or YYYYMM. Note that the period
arguments P1 and P2are not date values

SELECT PERIOD_DIFF(200802,200703);
PERIOD_DIFF() -> 11

QUARTER() Return the quarter from a date argument

QUARTER(date)

SELECT QUARTER('2008-04-01');
 Name Description

-> 2

Converts seconds to 'HH:MM:SS' format

SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours,
minutes, and seconds, as a TIME value

SELECT SEC_TO_TIME(2378);
-> '00:39:38'

SEC_TO_TIME()

Return the second (0-59)

SECOND(time)

SELECT SECOND('10:05:03');
-> 3

SECOND()

Convert a string to a date

STR_TO_DATE(str,format)

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
-> '2013-05-01'

SELECT STR_TO_DATE('May 1, 2013','%M %d,

%Y');
-> '2013-05-01'
SELECT STR_TO_DATE('9','%m');
-> '0000-09-00'

SELECT STR_TO_DATE('9','%s');
STR_TO_DATE() -> '00:00:09'

SUBDATE() Synonym for DATE_SUB() when invoked with three

 Name Description

arguments

SUBDATE(date,INTERVAL expr unit), 
SUBDATE(expr,days)

SELECT DATE_SUB('2008-01-02', INTERVAL 31

DAY);

-> '2007-12-02'
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
-> '2007-12-02'

SELECT SUBDATE('2008-01-02 12:00:00', 31);

-> '2007-12-02 12:00:00'

Subtract times

SUBTIME(expr1,expr2)

SUBTIME() returns expr1 − expr2 expressed as a
value in the same format as expr1. 

expr1 is a time or datetime expression,

and expr2 is a time expression.

SELECT SUBTIME('2007-12-31 23:59:59.999999','1

1:1:1.000002');
-> '2007-12-30 22:58:58.999997'

SELECT SUBTIME('01:00:00.999999',
'02:00:00.999998');
SUBTIME() -> '-00:59:59.999999'

SYSDATE() Return the time at which the function executes

 Name Description

Returns the current date and time as a value

in 'YYYY-MM-DD HH:MM:SS'

Extract the time portion of the expression passed

TIME(expr)

Extracts the time part of the time or datetime

expression expr and returns it as a string.

SELECT TIME('2003-12-31 01:02:03');

-> '01:02:03'

SELECT TIME('2003-12-31 01:02:03.000123');

-> '01:02:03.000123'

TIME()

Format as time

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function, but

the format string may contain format specifiers only
for hours, minutes, seconds, and microseconds.
Other specifiers produce a NULL value or 0.

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I

%l');
-> '100 100 04 04 4'

TIME_FORMAT()

TIME_TO_SEC() Return the argument converted to seconds

TIME_TO_SEC(time)

SELECT TIME_TO_SEC('22:23:00');
 Name Description

-> 80580

SELECT TIME_TO_SEC('00:39:38');
-> 2378

Subtract time

TIMEDIFF(expr1,expr2)
TIMEDIFF() returns expr1 − expr2 expressed as a
time value. expr1 andexpr2 are time or date-and-
time expressions, but both must be of the same
type.

SELECT TIMEDIFF('2000:01:01 00:00:00',

-> '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'

SELECT TIMEDIFF('2008-12-31 23:59:59.000001',

-> '2008-12-30 01:01:01.000002');
-> '46:58:57.999999'

TIMEDIFF()

With a single argument, this function returns the

date or datetime expression; with two arguments,
the sum of the arguments

TIMESTAMP(expr), 

TIMESTAMP(expr1,expr2)

SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'

SELECT TIMESTAMP('2003-12-31
12:00:00','12:00:00');
TIMESTAMP() -> '2004-01-01 00:00:00'

TIMESTAMPADD() Add an interval to a datetime expression

 Name Description

TIMESTAMPADD(unit, interval, datetime_expr)

Adds the integer expression interval to the date or

datetime expression datetime_expr.

The unit for interval is given by the unit argument,

which should be one of the following
values: MICROSECOND (microseconds), SECOND,MI
NUTE, HOUR, DAY, WEEK, MONTH, QUARTER,
or YEAR.

SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00'

SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'

TIMESTAMPDIFF() Subtract an interval from a datetime expression

TIMESTAMPDIFF(unit, datetime_expr1,
datetime_expr2)

Returns datetime_expr2 − datetime_expr1,
where datetime_expr1 anddatetime_expr2 are
date or datetime expressions. One expression may
be a date and the other a datetime; a date value is
treated as a datetime having the time
part '00:00:00' where necessary

SELECT TIMESTAMPDIFF(MONTH,'2003-02-
01','2003-05-01');
-> 3
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-
01-01');
-> -1
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-
01','2003-05-01 12:05:55');
 Name Description

-> 128885

Return the date argument converted to days

TO_DAYS(date)

SELECT TO_DAYS('2007-10-07');
-> 733321

TO_DAYS() From 0000-00-00

Return the date or datetime argument converted to

seconds since Year 0

TO_SECONDS(expr)

Given a date or datetime expr, returns a the number

of seconds since the year 0

SELECT TO_SECONDS('2009-11-29');
-> 63426672000

TO_SECONDS()

UNIX_TIMESTAMP() Return a Unix timestamp

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp
(seconds since '1970-01-01 00:00:00' UTC) as an
unsigned integer.

If UNIX_TIMESTAMP() is called with

a date argument, it returns the value of the
argument as seconds since '1970-01-01
00:00:00' UTC

The date argument may be a DATE, DATETIME,

or TIMESTAMP string, or a number
in YYMMDD, YYMMDDHHMMSS, YYYYMMDD,
 Name Description

orYYYYMMDDHHMMSS format

SELECT UNIX_TIMESTAMP();
-> 1447431666
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
-> 1447431619

mysql> SELECT UNIX_TIMESTAMP('1970-01-01

05:30:01');

+---------------------------------------+

| UNIX_TIMESTAMP('1970-01-01 05:30:01') |

+---------------------------------------+

| 1|

+---------------------------------------+

Return the current UTC date

Returns the current UTC date as a value in 'YYYY-

MM-DD' or YYYYMMDD format

SELECT UTC_DATE()
UTC_DATE()
-> '2003-08-14'
Return the current UTC time

UTC_TIME, UTC_TIME()

Returns the current UTC time as a value

in 'HH:MM:SS' or HHMMSS.uuuuuuformat

SELECT UTC_TIME();
UTC_TIME()
-> '18:07:53'
UTC_TIMESTAMP() Return the current UTC date and time

Returns the current UTC date and time as a value

 Name Description

in 'YYYY-MM-DD
HH:MM:SS'or YYYYMMDDHHMMSS.uuuuuu format

SELECT UTC_TIMESTAMP();
-> '2003-08-14 18:08:04'

Return the week number

WEEK(date)

 If the week containing January 1 has 4 or more

days in the new year, it is week 1.
 Otherwise, it is the last week of the previous
year, and the next week is week 1.
SELECT WEEK('2011-01-01');
-> 7

WEEK()

Return the weekday index

WEEKDAY(date)

Returns the weekday index for date (0 =

Monday, 1 = Tuesday, … 6 = Sunday).

SELECT WEEKDAY('2008-02-03 22:23:00');

WEEKDAY() -> 6

WEEKOFYEAR() Return the calendar week of the date (1-53)

WEEKOFYEAR(date)
Returns the calendar week of the date as a number
in the range from 1 to 53.
 Name Description

SELECT WEEKOFYEAR('2008-02-20');
-> 8

Return the year

YEAR(date)
Returns the year for date, in the
range 1000 to 9999, or 0 for the “zero” date

SELECT YEAR('1987-01-01');
-> 1987

YEAR()

Return the year and week

YEARWEEK(date)

Returns year and week for a date. The year in the

result may be different from the year in the date
argument for the first and the last week of the year
SELECT YEARWEEK('1987-01-01');
YEARWEEK() -> 198652