MySQL Interval for Date and Time

Date and Time in MySQL – Part 7

Foreword: 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: 13 Apr 2016

Introduction

This is part 7 of my series, Date and Time in MySQL. 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. You should have read the previous parts of the series, as this is a continuation.

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.

Formatted Figure - Unit
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.

Adding and Subtracting Intervals
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

Broad Network

Related Articles

MySQL Interval for Date and Time

Date and Time in MySQL – Part 7

Introduction

Related Links

Comments