MySQL Interval for Date and Time
Date and Time in MySQL – Part 7
Forward: In this part of the series, I explain how to represent the difference between two dates, the difference between two times and the difference between two datetimes.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
Interval
Time goes on from seconds to minutes to hours to days to weeks to months to quarters and to years. What is the difference between two instants in this line? This difference is an interval. The interval can be expressed in seconds, or minutes, or minutes and seconds, or hours, or hours and minutes and seconds, or days, or days and hours and minutes and seconds, or weeks, or weeks and days and minutes and seconds, or months, or months and weeks and days and hours and minutes and seconds. As you can see there needs to be proper classification of interval representations in order to use intervals properly.
You can give an interval in seconds, minutes, hours, days, weeks, months or years. You can also give an interval as a combination of these items. MySQL combinations are: 'SECONDS.MICROSECONDS', 'MINUTES:SECONDS.MICROSECONDS', 'MINUTES:SECONDS', 'HOURS:MINUTES:SECONDS.MICROSECONDS', 'HOURS:MINUTES:SECONDS', 'HOURS:MINUTES', 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS', 'DAYS HOURS:MINUTES:SECONDS', 'DAYS HOURS:MINUTES', 'DAYS HOURS', 'YEARS-MONTHS'.
Writing Intervals
I will give you some examples before I give you a list that governs the coding. In MySQL, if you want to write 5 years, you will write that like:
5 YEAR
Here, 5 is the number of years and YEAR (without s) is the unit. This is similar to something like 5 Kg of potatoes, where 5 is the number of kilograms and Kg is the unit. If you want to write 13 seconds, you will write that like:
13 SECOND
Here, 13 is the number of seconds and SECOND (without s) is the unit. If you want to write 6 days 3 hours 9 minutes and 4 seconds, you would write:
'6 3:9:4' DAY_SECOND
Here, 6 3:9:4 is the figure (number) and DAY_SECOND is the unit. The whole expression is not looking convenient but I will give you a list shortly that governs the figures and units. If you want to write 1 minute and 1 second, you would write:
'1:1' MINUTE_SECOND
Here, 1:1 is the figure and MINUTE_SECOND is the unit (without s).
Note the presence of the quotes for multiple-digit figures.
Interval List
You just have to accept what is in the list. The list gives the name of a figure or figure structure on the left, and the unit on the right.
MICROSECONDS - MICROSECOND
SECONDS - SECOND
MINUTES - MINUTE
HOURS - HOUR
DAYS - DAY
WEEKS - WEEK
MONTHS - MONTH
QUARTERS - QUARTER
YEARS - YEAR
'SECONDS.MICROSECONDS' - SECOND_MICROSECOND
'MINUTES:SECONDS.MICROSECONDS' - MINUTE_MICROSECOND
'MINUTES:SECONDS' - MINUTE_SECOND
'HOURS:MINUTES:SECONDS.MICROSECONDS' - HOUR_MICROSECOND
'HOURS:MINUTES:SECONDS' - HOUR_SECOND
'HOURS:MINUTES' - HOUR_MINUTE
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' - DAY_MICROSECOND
'DAYS HOURS:MINUTES:SECONDS' - DAY_SECOND
'DAYS HOURS:MINUTES' - DAY_MINUTE
'DAYS HOURS' - DAY_HOUR
'YEARS-MONTHS' - YEAR_MONTH
Units
The units for single-digit figures are: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER.
The units for multiple-digit figures are: SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH. A combination of two items is linked with an underscore character.
Hey, you can add interval to a date or datetime; you can also subtract interval from a date or datetime. The following examples copied from the specification illustrate this; note the use of the reserved word INTERVAL and the addition and subtraction signs in the examples (for simplicity, do not worry yourself much on how the addition or subtraction is really done – just know that MySQL can do them for you correctly if you write the statements correctly):
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
Output (return value) is: '2009-01-01 00:00:00'
SELECT INTERVAL 1 DAY + '2008-12-31';
Output is: '2009-01-01'
SELECT '2005-01-01' - INTERVAL 1 SECOND;
Output is: '2004-12-31 23:59:59'
SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
Output is: '2001-01-01 00:00:00'
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
Output is: '2011-01-01 23:59:59'
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
Output is: '2101-01-01 00:01:00'
SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
Output is: '2004-12-30 22:58:59'
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
Output is: '1899-12-30 14:00:00'
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
Output is: '1997-12-02'
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
Output is: '1993-01-01 00:00:01.000001'
Note: in the statements, after the reserved word, INTERVAL, you have the figure for the interval and then the unit for the interval.
That is it for this part of the series. We stop here and continue in the next part.
Chrys