Mysql dates() functions overview
Date: July 2nd, 2007 No comments
A very good overview of the Date and Time functions in Mysql from databasejournal ( from 2003). Here i have stuck just to the sql. Please see the link to read all the details.
# SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
# 2003-07-27
# SELECT DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE);
# 2003-07-13 00:39:01
# SELECT DATE_ADD('2003-07-13', INTERVAL -1 MINUTE);
# 2003-07-12 23:59:00
# SELECT DATE_ADD('2003-07-13', INTERVAL '-22:14' HOUR_MINUTE);
# 2003-07-12 01:46:00
# SELECT DATE_ADD('2003-07-14', INTERVAL -'22:14' HOUR_MINUTE);
# 2003-07-13 23:38:00
# SELECT PERIOD_ADD(200312,43);
# 200707
# SELECT PERIOD_ADD(0312,-32);
# 200104
# SELECT PERIOD_ADD(200312,43);
# 200707
# SELECT PERIOD_ADD(0312,-32);
# 200104
# SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
# 2003-07-27
# SELECT DATE_FORMAT('2003-07-14','%b %d,%Y');
# Jul 14,2003
# DATE_SUB('2003-07-14', INTERVAL 14 DAY)
# 2003-06-30
# SELECT DAYNAME('2003-07-14');
# Monday
# SELECT DAYOFMONTH('2003-07-14');
# 14
# SELECT DAYOFWEEK('2003-07-14');
# 2
# SELECT DAYOFYEAR('2003-07-14');
# 195
# SELECT EXTRACT(HOUR_MINUTE FROM '2003-07-14 12:32:14');
# 1232
# SELECT FROM_DAYS(731682);
# 2003-04-12
# SELECT FROM_UNIXTIME(1064431682);
# 2003-09-24 21:28:02
# SELECT HOUR('12:32:15');
# 12
# SELECT MINUTE('12:32:12');
# 32
# SELECT MONTH('2003-07-12');
# 7
# SELECT MONTHNAME('2003-07-12');
# July
# SELECT PERIOD_ADD(200307,6);
# 200401
# SELECT PERIOD_DIFF(200307,200209);
# 10
# SELECT QUARTER('2003-07-12');
# 3
# SELECT SEC_TO_TIME(2349);
# 00:39:09
# SELECT SECOND('12:32:11');
# 11
# SELECT TIME_FORMAT('2003-07-14 11:23:19','%r');
# 11:23:19 AM
# SELECT UNIX_TIMESTAMP();
# 1050315703
# SELECT WEEK('2003-07-12');
# 27
%a Abbreviation of the day (from Sun-Sat)
%b Abbreviation of the month (from Jan-Dec)
%c Numeric month (from 1-12)
%D Numeric day of the month with suffix (1st, 2nd, and so on)
%d Numeric day of the month with two digits(from 00-31)
%e Numeric day of the month with one or two digits(from 0-31)
%H Hour (from 00-23)
%h Hour (from 01-12)
%i Minutes (from 00-59)
%I Hour (from 01-12)
%j Day of the year (from 001-366)
%k Hour with one or two digits (from 0-23)
%l Hour with one digit (from 1-12)
%M Month name (from January-December)
%m Numeric month (from 01-12)
%p A.M. or P.M.
%r 12-hour time (hh:mm:ss A.M.or P.M.)
%S Seconds (from 00-59)
%s Seconds (from 00-59)
%T 24 hour time (hh:mm:ss)
%U Week (from 00-53, Sunday being the first day of the week)
%u Week (from 00-53, Monday being the first day of the week)
%V Week (from 01-53, Sunday being the first day of the week)
%v Week (from 01-53, Monday being the first day of the week)
%W Name of the day in the week (from Sunday-Saturday)
%w Day of the week (from 0 - Sunday, to 6 - Saturday)
%X Four-digit numeric year for the week (Sunday being the first day of the week)
%x Four-digit numeric year for the week (Monday being the first day of the week)
%Y Four-digit numeric year
%y Two-digit numeric year
%% Percentage sign (escaped)
ADDDATE() same as DATE_ADD()
CURDATE() same as CURRENT_DATE()
SUBDATE() same as DATE_SUB()
CURRENT_TIMESTAMP() same as NOW()
CURTIME() same as CURRENT_TIME()