Date Time Operation in MySql

##Convert Time Zone###->SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
->SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

 

/////////////////////BESIC FUNTION FOR SYSTEM DATE & TIME///////////////#Select Now Function->SELECT now();
#Now Function With SLEEP
->SELECT NOW(), SLEEP(3), NOW();#SYSDATE WITH SLEEP
->SELECT SYSDATE(), SLEEP(2), SYSDATE();#Select Current Date
->SELECT CURDATE();#Select Current Date
->SELECT CURTIME();#Find Number Of Days Between Two Date
->SELECT DATEDIFF('2007-06-26 23:59:59','2007-04-20');
#Return Day Of Week->SELECT DAYOFWEEK('2007-02-03');
#Return Day Of Year
->SELECT DAYOFYEAR('2007-02-03');
#Extract Function::::EXTRACT(unit FROM date)->SELECT EXTRACT(YEAR FROM '2007-06-26');#Extract YEAR & MONTH->SELECT EXTRACT(YEAR_MONTH FROM '2001-06-26 01:02:03');
#Extract DAY & MINUTE->SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
#Using Make Date Function->SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);

////////////DIFFERENT DATE FORMATE//////////////////////////
#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,'INTERNAL')  '%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(TIME,'INTERNAL')  '%H%i%s'

->SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

////////////////SYSTEM DATE AND TIME IN MYSQL///////////////Specifier                     Description %a         Abbreviated weekday name (Sun..Sat)
%b         Abbreviated month name (Jan..Dec)
%c         Month, numeric (0..12)
%D         Day of the month with English suffix (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 
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X 
%v Week (01..53), where Monday is the first day of the week; used with %x 
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V 
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v 
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above

E.G::::::---->
->SELECT DATE_FORMAT('2009-10-05', '%W %M %Y') "DATE IS";
->SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j')'DATE IS';
->SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');

////////////////////WEEK FUNCTION//////////////////////Mode  First day of week  Range  Week 1 is the first week …
0  Sunday    0-53  with a Sunday in this year
1  Monday    0-53  with more than 3 days this year
2  Sunday    1-53  with a Sunday in this year
3  Monday    1-53  with more than 3 days this year
4  Sunday    0-53  with more than 3 days this year
5  Monday    0-53  with a Monday in this year
6  Sunday    1-53  with more than 3 days this year
7  Monday    1-53  with a Monday in this year

->SELECT WEEK('2005-06-26');
        -> 26
-> SELECT WEEK('2005-06-26',0);
        -> 26
-> SELECT WEEK('2005-06-26',1);
        -> 25
-> SELECT WEEK('2005-06-12',1);
        -> 23

///////DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)///////////unit/Value            Expected expr Format  MICROSECOND       MICROSECONDS
SECOND                      SECONDS
MINUTE                    MINUTES
HOUR                         HOURS
DAY                            DAYS
WEEK                         WEEKS
MONTH                     MONTHS
QUARTER               QUARTERS
YEAR                        YEARS
SECOND_MICROSECOND  'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND  'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND   'MINUTES:SECONDS'
HOUR_MICROSECOND  'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND   'HOURS:MINUTES:SECONDS'
HOUR_MINUTE   'HOURS:MINUTES'
DAY_MICROSECOND  'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND   'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE   'DAYS HOURS:MINUTES'
DAY_HOUR   'DAYS HOURS'
YEAR_MONTH   'YEARS-MONTHS'

mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
        -> '2009-01-01'
mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
        -> '2004-12-31 23:59:59'
mysql> SELECT DATE_ADD('2000-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2001-01-01 00:00:00'
mysql> SELECT DATE_ADD('2010-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2011-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'