MENU


Date and Time

Current Date and Time
SELECT CURRENT_DATE, CURRENT_DATE(), CURDATE();
-- synonyms		 2014-04-27
2014-04-27
2014-04-27
SELECT CURRENT_TIME, CURRENT_TIME(), CURTIME();
-- synonyms		 13:16:54
13:16:54
13:16:54
SELECT NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP();
-- synonyms		 2014-04-27 13:16:54
……
SELECT NOW(2), SLEEP(1), NOW(2); 2014-04-27 13:16:54.28
0
2014-04-27 13:16:54.28
SELECT SYSDATE(), SLEEP(1), SYSDATE(); 2014-04-27 13:16:54
0
2014-04-27 13:16:55
SELECT SYSDATE(2); 2014-04-27 13:16:55.72
SELECT UNIX_TIMESTAMP();
-- seconds since 1970-01-01 00:00:00		 1398576430
SELECT UNIX_TIMESTAMP(
'2014-04-27 13:28:00');
-- seconds since 1970-01-01 00:00:00		 1398576480
SELECT UTC_DATE, UTC_DATE()+0;
-- synonyms		 2014-04-27
20140427
SELECT UTC_TIME, UTC_TIME()+0;
-- synonyms		 05:30:42
53042
SELECT UTC_TIMESTAMP, UTC_TIMESTAMP()+0;
-- synonyms		 2014-04-27 05:32:57
20140427053257

Additions and Subtractions
SELECT DATE_ADD(
'2014-04-27', INTERVAL 15 DAY),
ADDDATE(
'2014-04-27', INTERVAL 15 DAY);
-- synonyms		 2014-05-12
2014-05-12
SELECT ADDDATE('2014-04-27', 15);
-- second argument treated as days		 2014-05-12
SELECT DATE_SUB(
'2014-04-27', INTERVAL 15 DAY),
SUBDATE(
'2014-04-27', INTERVAL 15 DAY);
-- synonyms		 2014-04-12
2014-04-12
SELECT SUBDATE('2014-04-27', 15);
-- second argument treated as days		 2014-04-12
SELECT ADDTIME('2014-04-27 13:30:00', '1 05:30:30.123456'); 2014-04-28
19:00:30.123456
SELECT ADDTIME('13:30:00',
'1 05:30:30.123456');		 43:00:30.123456
SELECT SUBTIME('13:30:00', '05:30:30.123456'); 07:59:29.876544
SELECT DATEDIFF(
'2014-04-27 14:00:00','2015-04-27');
-- time not used		 -365
SELECT TIMEDIFF(
'2014-04-27 14:00:00',
'2014-03-27 06:30:30');		 751:29:30
SELECT TIMESTAMPDIFF(DAY,
'2014-04-27 14:00:00','2013-03-27');		 -396
SELECT TIMESTAMPADD(WEEK,15,
'2014-04-27 13:30:30.1');		 2014-08-10
13:30:30.100000
SELECT PERIOD_DIFF(201311,201404);
-- 5 months		 -5
SELECT PERIOD_ADD(201404,12);
-- 12 months		 201504
SELECT TIMESTAMP(
'2007-02-10 14:30:00','05:10:10');		 2007-02-10
19:40:10
SELECT EXTRACT(
YEAR FROM '2014-04-27');		 2014
INTERVAL units:
 MICROSECOND: 123456
SECOND: 30
MINUTES: 30
HOUR: 12
DAY: 4
WEEK: 25
MONTH: 6
QUARTER: 2
YEAR: 2000
SECOND_MICROSECOND: ’30.123456’
MINUTE_MICROSECOND: ’30:30.123456’
MINUTE_SECOND: ’30:30’
HOUR_MICROSECOND: ’06:30:30.123456’
HOUR_SECOND: ’06:30:30’
HOUR_MINUTE: ’06:30’
DAY_MICROSECOND: ’15 06:30:30.123456’
DAY_SECOND: ’15 06:30:30’
DAY_MINUTE: ’15 06:30’
DAY_HOUR: ’15 06’
YEAR_MONTH: ’15-06’

Formatting
SET @d =
'2014-04-27 14:30:00.123456';
SELECT YEAR(@d),MONTH(@d),DAY(@d),
HOUR(@d),MINUTE(@d),SECOND(@d),
MICROSECOND(@d);
-- DAYOFMONTH() is a synonym for DAY()		 2014
4
27
14
30
0
123456
SELECT DATE(
'2007-05-10 14:30:30.123456');		 2007-05-10
SELECT TIME(
'2007-05-10 14:30:30.123456');		 14:30:30.
123456
SELECT DAYOFWEEK('2007-02-10');
-- 1 to 7, SUNDAY to SATURDAY		 7
SELECT WEEKDAY('2007-02-10');
-- 0 to 6, MONDAY TO SUNDAY		 5
SELECT DAYNAME('2007-02-10'); Saturday
SELECT DAYOFYEAR('2007-05-10');
-- 1 to 366		 130
SELECT MAKEDATE(2014,100);
-- 100 is day of the year		 2014-04-10
SELECT MAKETIME(12,30,30); 12:30:30
SELECT WEEK('2014-12-31'); 52
SELECT WEEK('2014-12-25'),
WEEKOFYEAR('2014-12-25');		 51
52
SELECT YEARWEEK('2014-12-25'); 201451
SELECT MONTHNAME('2007-02-10'); February
SELECT QUARTER('2007-02-10'); -- 1 to 4 1
SELECT LAST_DAY('2007-02-10'); 2007-02-28
SELECT FROM_DAYS(1000); 0002-09-27
SELECT FROM_UNIXTIME(10); 1970-01-01
08:00:10
SELECT FROM_UNIXTIME(10)+0; 19700101
080010
SELECT SEC_TO_TIME(12345); 03:25:45
SELECT TIME_TO_SEC('03:25:45'); 12345
SELECT TO_DAYS('0000-02-01'); 32
SELECT TO_SECONDS('0000-02-01'); 2764800
SELECT DATE_FORMAT(
'2014-04-27 12:00:00' , '%Y,%M');		 2014,April
SELECT TIME_FORMAT('100:30:30.123456','%f'); 123456
SELECT STR_TO_DATE('2014,January','%Y,%M'); 2014-01-00
SELECT FROM_UNIXTIME(10,"%Y %D %M"); 1970 1st January
SELECT GET_FORMAT(DATE,'USA'); %m.%d.%Y
SELECT GET_FORMAT(DATE,'JIS'); %Y-%m-%d
SELECT GET_FORMAT(DATE,'ISO'); %Y-%m-%d
SELECT GET_FORMAT(TIME,'EUR'); %H.%i.%s
SELECT GET_FORMAT(DATETIME,'INTERNAL'); %Y%m%d
%H%i%s
SELECT CONVERT_TZ(
'2014-04-27 14:00:00','+00:00','+08:00');		 2014-04-27
22:00:00
Format Specifier Description Example
%a Abbreviated weekday name Sun
%b Abbreviated month name Jan
%c Month, numeric 2
%D Day of the month, with suffix 2nd
%d Day of the month, numeric 02
%e Day of the month, numeric 2
%f Microseconds 123456
%H Hour (00…23) 18
%h Hour (01…12) 12
%I Hour (01…12) 12
%i Minutes, numeric 09
%j Day of year (001…366) 002
%k Hour (0…23) 18
%l Hour(1…12) 12
%M Month name January
%m Month, numeric 02
%p AM or PM AM
%r Time 12:30:30 AM
%S Seconds 05
%s Seconds 05
%T Time 18:30:30
%U Week(0…53), Sunday first 05
%u Week(0…53), Monday first 05
%V Week(1…53), Sunday first 05
%v Week(1…53), Monday first 05
%W Weekday name Sunday
%w Day of the week (0=Sunday) 0
%X Year for the week, Sunday first 2000
%x Year for the week, Monday first 2000
%Y Year, numeric 2000
%y Year, numeric 99
%% Literal % character %