Date and Time

Current Date and Time

CURRENT_DATE, CURRENT_DATE(), and CURDATE() are synonyms returning today's date. CURRENT_TIME, CURRENT_TIME(), and CURTIME() return the current time. NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP() are all synonyms returning the current date and time; an optional fractional-seconds precision argument (e.g. NOW(2)) requests sub-second precision. SYSDATE() is similar to NOW() but re-reads the clock at the moment it executes rather than at the start of the statement — noticeable in combination with SLEEP(). UNIX_TIMESTAMP() returns the number of seconds since 1970-01-01 00:00:00 UTC; UNIX_TIMESTAMP(date) converts a given date/time value instead. UTC_DATE, UTC_TIME, and UTC_TIMESTAMP (with or without parentheses) return the current UTC date, time, and date-time.

SELECT NOW(2), SLEEP(1), NOW(2);
SELECT SYSDATE(), SLEEP(1), SYSDATE();

The two NOW(2) calls return the same value (NOW() is fixed for the duration of the statement), while the two SYSDATE() calls differ by roughly one second.

-- Current date/time values are non-deterministic (they depend on when
-- the statement runs), so no fixed result is shown here.
SELECT NOW(), CURDATE(), CURTIME(), UNIX_TIMESTAMP();

-- Current date/time values are non-deterministic (they depend on when
-- the statement runs), so no fixed result is shown here.
SELECT NOW(), CURDATE(), CURTIME(), UNIX_TIMESTAMP();

Addition and Subtraction

DATE_ADD(date, INTERVAL expr unit) and ADDDATE(date, INTERVAL expr unit) are synonyms adding an interval to a date; DATE_SUB()/SUBDATE() subtract one. When ADDDATE()/ SUBDATE() are called with a plain integer instead of an INTERVAL, the integer is treated as a number of days. ADDTIME(expr1, expr2) and SUBTIME(expr1, expr2) add/subtract a time value or interval string from a time or date-time. DATEDIFF(expr1, expr2) returns the difference in whole days (ignoring any time-of-day component). TIMEDIFF(expr1, expr2) returns the difference as a TIME value. TIMESTAMPDIFF(unit, datetime1, datetime2) returns the difference expressed in the given unit; TIMESTAMPADD(unit, expr, datetime) adds expr units to datetime. PERIOD_DIFF(P1,P2) and PERIOD_ADD(P,N) operate on periods in YYYYMM or YYMM format. TIMESTAMP(expr1 [, expr2]) combines a date and time into one date-time value. EXTRACT(unit FROM date) pulls a single component (see interval units below) out of a date.

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY);
SELECT ADDDATE('2014-04-27', 15);
SELECT DATEDIFF('2014-04-27 14:00:00','2015-04-27');
SELECT TIMESTAMPDIFF(DAY, '2014-04-27 14:00:00','2013-03-27');
SELECT TIMESTAMPADD(WEEK, 15, '2014-04-27 13:30:30.1');
SELECT PERIOD_DIFF(201311,201404);
SELECT PERIOD_ADD(201404,12);
SELECT EXTRACT(YEAR FROM '2014-04-27');

'2014-05-12'; '2014-05-12' (the plain 15 is treated as days); -365 (2014-04-27 is 365 days before 2015-04-27); -396; '2014-08-10 13:30:30.1'; -5 (201311 is 5 months before 201404); 201504 (12 months after 201404); 2014

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY);

SELECT ADDDATE('2014-04-27', 15);

SELECT DATEDIFF('2014-04-27 14:00:00','2015-04-27');

SELECT TIMESTAMPDIFF(DAY, '2014-04-27 14:00:00','2013-03-27');

SELECT TIMESTAMPADD(WEEK, 15, '2014-04-27 13:30:30.1');

SELECT PERIOD_DIFF(201311,201404);

SELECT PERIOD_ADD(201404,12);

SELECT EXTRACT(YEAR FROM '2014-04-27');

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY);

SELECT ADDDATE('2014-04-27', 15);

SELECT DATEDIFF('2014-04-27 14:00:00','2015-04-27');

SELECT TIMESTAMPDIFF(DAY, '2014-04-27 14:00:00','2013-03-27');

SELECT TIMESTAMPADD(WEEK, 15, '2014-04-27 13:30:30.1');

SELECT PERIOD_DIFF(201311,201404);

SELECT PERIOD_ADD(201404,12);

SELECT EXTRACT(YEAR FROM '2014-04-27');

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY):

DATE_ADD('2014-04-27', INTERVAL 15 DAY)
2014-05-12

SELECT ADDDATE('2014-04-27', 15):

ADDDATE('2014-04-27', 15)
2014-05-12

SELECT DATEDIFF('2014-04-27 14:00:00','2015-04-27'):

DATEDIFF('2014-04-27 14:00:00','2015-04-27')
-365

SELECT TIMESTAMPDIFF(DAY, '2014-04-27 14:00:00','2013-03-27'):

TIMESTAMPDIFF(DAY, '2014-04-27 14:00:00','2013-03-27')
-396

SELECT TIMESTAMPADD(WEEK, 15, '2014-04-27 13:30:30.1'):

TIMESTAMPADD(WEEK, 15, '2014-04-27 13:30:30.1')
2014-08-10 13:30:30.1

SELECT PERIOD_DIFF(201311,201404):

PERIOD_DIFF(201311,201404)
-5

SELECT PERIOD_ADD(201404,12):

PERIOD_ADD(201404,12)
201504

SELECT EXTRACT(YEAR FROM '2014-04-27'):

EXTRACT(YEAR FROM '2014-04-27')
2014

Interval units accepted after INTERVAL expr: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, and the composite forms SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, and YEAR_MONTH — whose expr is a quoted, colon/space-delimited compound value, e.g. INTERVAL '15 06:30:30' DAY_SECOND for 15 days, 6 hours, 30 minutes, 30 seconds.

Formatting and Extraction

YEAR(), MONTH(), DAY() (synonym: DAYOFMONTH()), HOUR(), MINUTE(), SECOND(), and MICROSECOND() each pull that single field out of a date-time value. DATE(expr) and TIME(expr) extract the date, respectively time, portion of a date-time. DAYOFWEEK(date) returns 1–7 for Sunday–Saturday; WEEKDAY(date) returns 0–6 for Monday–Sunday; DAYNAME(date) returns the weekday's name. DAYOFYEAR(date) returns 1–366. MAKEDATE(year, dayofyear) and MAKETIME(hour, minute, second) build a date/time from components. WEEK(date)/WEEKOFYEAR(date) and YEARWEEK(date) return the calendar week. MONTHNAME(date) returns the month's name. QUARTER(date) returns 1–4. LAST_DAY(date) returns the date of the last day of that month. FROM_DAYS(N) converts a day-number back to a date; FROM_UNIXTIME(N [, format]) converts a Unix timestamp to a date-time. SEC_TO_TIME(N) and TIME_TO_SEC(time) convert between a TIME value and a count of seconds. TO_DAYS(date) and TO_SECONDS(date) convert a date to a day-number, respectively second-count, since year 0.

SELECT YEAR('2014-04-27 14:30:00.123456'),
MONTH('2014-04-27 14:30:00.123456'), DAY('2014-04-27 14:30:00.123456');
SELECT DAYOFWEEK('2007-02-10'), WEEKDAY('2007-02-10'), DAYNAME('2007-02-10');
SELECT MAKEDATE(2014,100);
SELECT WEEK('2014-12-25'), WEEKOFYEAR('2014-12-25'), YEARWEEK('2014-12-25');
SELECT LAST_DAY('2007-02-10');

2014, 4, 27; 7, 5, 'Saturday' (2007-02-10 was a Saturday); '2014-04-10' (day 100 of 2014); 51, 51, 201451; '2007-02-28'

SELECT YEAR('2014-04-27 14:30:00.123456'),
    MONTH('2014-04-27 14:30:00.123456'), DAY('2014-04-27 14:30:00.123456');

SELECT DAYOFWEEK('2007-02-10'), WEEKDAY('2007-02-10'), DAYNAME('2007-02-10');

SELECT MAKEDATE(2014,100);

SELECT WEEK('2014-12-25'), WEEKOFYEAR('2014-12-25'), YEARWEEK('2014-12-25');

SELECT LAST_DAY('2007-02-10');

SELECT YEAR('2014-04-27 14:30:00.123456'),
    MONTH('2014-04-27 14:30:00.123456'), DAY('2014-04-27 14:30:00.123456');

SELECT DAYOFWEEK('2007-02-10'), WEEKDAY('2007-02-10'), DAYNAME('2007-02-10');

SELECT MAKEDATE(2014,100);

SELECT WEEK('2014-12-25'), WEEKOFYEAR('2014-12-25'), YEARWEEK('2014-12-25');

SELECT LAST_DAY('2007-02-10');

SELECT YEAR('2014-04-27 14:30:00.123456'), MONTH('2014-04-27 14:30:00.123456'), DAY('2014-04-27 14:30:00.123456'):

YEAR('2014-04-27 14:30:00.123456')	MONTH('2014-04-27 14:30:00.123456')	DAY('2014-04-27 14:30:00.123456')
2014	4	27

SELECT DAYOFWEEK('2007-02-10'), WEEKDAY('2007-02-10'), DAYNAME('2007-02-10'):

DAYOFWEEK('2007-02-10')	WEEKDAY('2007-02-10')	DAYNAME('2007-02-10')
7	5	Saturday

SELECT MAKEDATE(2014,100):

MAKEDATE(2014,100)
2014-04-10

SELECT WEEK('2014-12-25'), WEEKOFYEAR('2014-12-25'), YEARWEEK('2014-12-25'):

WEEK('2014-12-25')	WEEKOFYEAR('2014-12-25')	YEARWEEK('2014-12-25')
51	51	201451

SELECT LAST_DAY('2007-02-10'):

LAST_DAY('2007-02-10')
2007-02-28

DATE_FORMAT(date, format) renders a date using a format string of the specifiers below; TIME_FORMAT(time, format) is the analogous function for a TIME value. STR_TO_DATE(str, format) parses a formatted string back into a date/time — the inverse of DATE_FORMAT(). GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}) returns a standard format string for a given locale/standard, for use with the two functions above. CONVERT_TZ(dt, from_tz, to_tz) converts a date-time between named or offset time zones.

SELECT DATE_FORMAT('2014-04-27 12:00:00' , '%Y,%M');
SELECT STR_TO_DATE('2014,January','%Y,%M');
SELECT GET_FORMAT(DATE,'USA');
SELECT CONVERT_TZ('2014-04-27 14:00:00','+00:00','+08:00');

'2014,April'; '2014-01-01' (STR_TO_DATE defaults missing day to 1); '%m.%d.%Y'; '2014-04-27 22:00:00'

SELECT DATE_FORMAT('2014-04-27 12:00:00' , '%Y,%M');

SELECT STR_TO_DATE('2014,January','%Y,%M');

SELECT GET_FORMAT(DATE,'USA');

SELECT CONVERT_TZ('2014-04-27 14:00:00','+00:00','+08:00');

SELECT DATE_FORMAT('2014-04-27 12:00:00' , '%Y,%M');

SELECT STR_TO_DATE('2014,January','%Y,%M');

SELECT GET_FORMAT(DATE,'USA');

SELECT CONVERT_TZ('2014-04-27 14:00:00','+00:00','+08:00');

SELECT DATE_FORMAT('2014-04-27 12:00:00' , '%Y,%M'):

DATE_FORMAT('2014-04-27 12:00:00' , '%Y,%M')
2014,April

SELECT STR_TO_DATE('2014,January','%Y,%M'):

STR_TO_DATE('2014,January','%Y,%M')
2014-01-01

SELECT GET_FORMAT(DATE,'USA'):

GET_FORMAT(DATE,'USA')
%m.%d.%Y

SELECT CONVERT_TZ('2014-04-27 14:00:00','+00:00','+08:00'):

CONVERT_TZ('2014-04-27 14:00:00','+00:00','+08:00')
2014-04-27 22:00:00

TIME_FORMAT(time, format) applies DATE_FORMAT()-style specifiers to a TIME value, and GET_FORMAT() covers the remaining standard/locale combinations beyond DATE/'USA' above:

SELECT FROM_UNIXTIME(10);

SELECT FROM_UNIXTIME(10, "%Y %D %M");

SELECT SEC_TO_TIME(12345);

SELECT TIME_TO_SEC('03:25:45');

SELECT TO_DAYS('0000-02-01');

SELECT TO_SECONDS('0000-02-01');

SELECT TIME_FORMAT('100:30:30.123456', '%f');

SELECT GET_FORMAT(DATE,'JIS'), GET_FORMAT(DATE,'ISO'),
       GET_FORMAT(TIME,'EUR'), GET_FORMAT(DATETIME,'INTERNAL');

SELECT FROM_UNIXTIME(10);

SELECT FROM_UNIXTIME(10, "%Y %D %M");

SELECT SEC_TO_TIME(12345);

SELECT TIME_TO_SEC('03:25:45');

SELECT TO_DAYS('0000-02-01');

SELECT TO_SECONDS('0000-02-01');

SELECT TIME_FORMAT('100:30:30.123456', '%f');

SELECT GET_FORMAT(DATE,'JIS'), GET_FORMAT(DATE,'ISO'),
       GET_FORMAT(TIME,'EUR'), GET_FORMAT(DATETIME,'INTERNAL');

SELECT FROM_UNIXTIME(10) (assumes a UTC session time zone):

FROM_UNIXTIME(10)
1970-01-01 00:00:10

SELECT FROM_UNIXTIME(10, "%Y %D %M"):

FROM_UNIXTIME(10, "%Y %D %M")
1970 1st January

SELECT SEC_TO_TIME(12345):

SEC_TO_TIME(12345)
03:25:45

SELECT TIME_TO_SEC('03:25:45'):

TIME_TO_SEC('03:25:45')
12345

SELECT TO_DAYS('0000-02-01'):

TO_DAYS('0000-02-01')
32

SELECT TO_SECONDS('0000-02-01'):

TO_SECONDS('0000-02-01')
2764800

SELECT TIME_FORMAT('100:30:30.123456', '%f'):

TIME_FORMAT('100:30:30.123456', '%f')
123456

SELECT GET_FORMAT(DATE,'JIS'), GET_FORMAT(DATE,'ISO'), GET_FORMAT(TIME,'EUR'), GET_FORMAT(DATETIME,'INTERNAL'):

GET_FORMAT(DATE,'JIS')	GET_FORMAT(DATE,'ISO')	GET_FORMAT(TIME,'EUR')	GET_FORMAT(DATETIME,'INTERNAL')
%Y-%m-%d	%Y-%m-%d	%H.%i.%s	%Y%m%d%H%i%s

Format Specifiers

%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of month with English suffix (1st, 2nd, 3rd, ...)
%d	Day of month, numeric, zero-padded (00..31)
%e	Day of month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour, 24-hour (00..23)
%h / %I	Hour, 12-hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour, 24-hour (0..23)
%l	Hour, 12-hour (1..12)
%M	Month name (January..December)
%m	Month, numeric, zero-padded (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss AM/PM)
%S / %s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U / %u	Week (00..53), Sunday-first / Monday-first
%V / %v	Week (01..53), used with %X / %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X / %x	Year for the week, Sunday-first / Monday-first, used with %V / %v
%Y	Year, 4 digits
%y	Year, 2 digits
%%	A literal % character

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY) AS plus_15_days,
       DATE_SUB('2014-04-27', INTERVAL 15 DAY) AS minus_15_days,
       DATEDIFF('2015-04-27', '2014-04-27') AS day_diff,
       TIMESTAMPDIFF(MONTH, '2013-03-27', '2014-04-27') AS month_diff;

SELECT YEAR(d), MONTH(d), DAY(d), DAYNAME(d), WEEK(d), QUARTER(d), LAST_DAY(d)
    FROM (SELECT '2014-04-27 14:30:00.123456' AS d) AS t;

SELECT DATE_FORMAT('2014-04-27 12:00:00', '%W, %M %D %Y at %h:%i %p') AS formatted;

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY) AS plus_15_days,
       DATE_SUB('2014-04-27', INTERVAL 15 DAY) AS minus_15_days,
       DATEDIFF('2015-04-27', '2014-04-27') AS day_diff,
       TIMESTAMPDIFF(MONTH, '2013-03-27', '2014-04-27') AS month_diff;

SELECT YEAR(d), MONTH(d), DAY(d), DAYNAME(d), WEEK(d), QUARTER(d), LAST_DAY(d)
    FROM (SELECT '2014-04-27 14:30:00.123456' AS d) AS t;

SELECT DATE_FORMAT('2014-04-27 12:00:00', '%W, %M %D %Y at %h:%i %p') AS formatted;

SELECT DATE_ADD('2014-04-27', INTERVAL 15 DAY) AS plus_15_days, DATE_SUB('2014-04-27', INTERVAL 15 DAY) AS minus_15_days, DATEDIFF('2015-04-27', '2014-04-27') AS day_diff, TIMESTAMPDIFF(MONTH, '2013-03-27', '2014-04-27') AS month_diff:

plus_15_days	minus_15_days	day_diff	month_diff
2014-05-12	2014-04-12	365	13

SELECT YEAR(d), MONTH(d), DAY(d), DAYNAME(d), WEEK(d), QUARTER(d), LAST_DAY(d) FROM (SELECT '2014-04-27 14:30:00.123456' AS d) AS t:

YEAR(d)	MONTH(d)	DAY(d)	DAYNAME(d)	WEEK(d)	QUARTER(d)	LAST_DAY(d)
2014	4	27	Sunday	17	2	2014-04-30

SELECT DATE_FORMAT('2014-04-27 12:00:00', '%W, %M %D %Y at %h:%i %p') AS formatted:

formatted
Sunday, April 27th 2014 at 12:00 PM

JSON	<<<	Data Manipulation Language	>>>	Spatial Analysis
last update: 17 Aug 2026

MENU

Date and Time

Current Date and Time

Addition and Subtraction

Formatting and Extraction

Format Specifiers