Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - Introduction to SQL Date and Time Handling

By: FYIcenter.com

Part:   1  2  3   4  5 

(Continued from previous part...)

What Are Date and Time Intervals?

A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL expression unit", where "unit" and "expression" should follow these rules:

unit                 expression value 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.MICROSECONDS' 
MINUTE_SECOND        'MINUTES:SECONDS' 
HOUR_MICROSECOND     'HOURS.MICROSECONDS' 
HOUR_SECOND          'HOURS:MINUTES:SECONDS' 
HOUR_MINUTE          'HOURS:MINUTES' 
DAY_MICROSECOND      'DAYS.MICROSECONDS' 
DAY_SECOND           'DAYS HOURS:MINUTES:SECONDS' 
DAY_MINUTE           'DAYS HOURS:MINUTES' 
DAY_HOUR             'DAYS HOURS' 
YEAR_MONTH           'YEARS-MONTHS' 

How To Increment Dates by 1?

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:

SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)
   FROM DUAL;
   1997-02-01

SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;
   1997-02-01

How To Decrement Dates by 1?

If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:

SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
   FROM DUAL;
   1997-02-28

SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
   1997-02-28

How To Calculate the Difference between Two Dates?

If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:

SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
   FROM DUAL;
   -1

How To Calculate the Difference between Two Time Values?

If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:

SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50')) 
   FROM DUAL;
   10:00:00

SELECT TIMEDIFF('1997-03-01 19:26:50.000123', 
   '1997-02-28 09:26:50.000000') FROM DUAL;
   34:00:00.000123

(Continued on next part...)

Part:   1  2  3   4  5