Calculating Intervals Between Dates
Problem
You want to know how long it is between dates.
Solution
Convert both dates to basic units and take the difference between the resulting values.
Discussion
The general procedure for calculating an interval between dates is to
convert both dates to a common unit in relation to a given reference
point, then take the difference. The range of values
you’re working with determines which conversions are
available. DATE, DATETIME, or
TIMESTAMP values dating back to
1970-01-01 00:00:00
GMT—the date of the Unix epoch—can be converted to
seconds elapsed since the epoch. If both dates lie within that range,
you can calculate intervals to an accuracy of one second. Older dates
from the beginning of the Gregorian calendar (1582) on can be
converted to day values and used to compute intervals in days. Dates
that begin earlier than either of these reference points present more
of a problem. In such cases, you may find that your programming
language offers computations that are not available or are difficult
to perform in SQL.
If so,
consider processing date values directly from within your API
language. (For example, the Date::Calc and
Date::Manip modules are available from the CPAN
for use within Perl scripts.)
To calculate an interval in days between date or date-and-time
values, convert them to days using TO_DAYS( ), then
take the difference:
mysql> SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days; +------+ | days | +------+ | 210 | +------+ ...