Recipe 6.17. Performing Leap Year Calculations


Problem

You need to perform a date calculation that must account for leap years. For example, the length of a month or a year depends on knowing whether the date falls in a leap year.

Solution

Know how to test whether a year is a leap year, and factor the result into your calculation.

Discussion

Date calculations are complicated by the fact that months don't all have the same number of days, and an additional headache is that February has an extra day during leap years. This recipe shows how to determine whether any given date falls within a leap year and how to take leap years into account when determining the length of a year or month.

Determining whether a date occurs in a leap year

To determine whether a date d falls within a leap year, obtain the year component using YEAR⁠(⁠ ⁠ ⁠) and test the result. The common rule-of-thumb test for leap years is "divisible by four," which you can test using the % modulo operator like this:

YEAR(d) % 4 = 0 

However, that test is not technically correct. (For example, the year 1900 is divisible by four, but is not a leap year.) For a year to qualify as a leap year, it must satisfy both of the following constraints:

  • The year must be divisible by four.

  • The year cannot be divisible by 100, unless it is also divisible by 400.

The meaning of the second constraint is that turn-of-century years are not leap years, except every fourth century. In SQL, you can express these conditions as follows:

(YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0)) 

Running our date_val table through both the rule-of-thumb leap-year test and the complete test produces the following results:

mysql> SELECT     -> d,     -> YEAR(d) % 4 = 0     ->   AS 'rule-of-thumb test',     -> (YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0))     ->   AS 'complete test'     -> FROM date_val; +------------+--------------------+---------------+ | d          | rule-of-thumb test | complete test | +------------+--------------------+---------------+ | 1864-02-28 |                  1 |             1 | | 1900-01-15 |                  1 |             0 | | 1987-03-05 |                  0 |             0 | | 1999-12-31 |                  0 |             0 | | 2000-06-04 |                  1 |             1 | +------------+--------------------+---------------+ 

As you can see, the two tests don't always produce the same result. In particular, the rule-of-thumb test fails for the year 1900; the complete test result is correct because it accounts for the turn-of-century constraint.

Note that because the complete leap-year test needs to check the century, it requires four-digit year values. Two-digit years are ambiguous with respect to the century, making it impossible to assess the turn-of-century constraint.

If you're working with date values within a program, you can perform leap-year tests with your API language rather than at the SQL level. Pull off the first four digits of the date string to get the year, and then test it. If the language performs automatic string-to-number conversion of the year value, this is easy. Otherwise, you must explicitly convert the year value to numeric form before testing it.

Perl, PHP:

$year = substr ($date, 0, 4); $is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);

Ruby:

year = date[0..3].to_i is_leap = (year.modulo(4) == 0) &&             (year.modulo(100) != 0 || year.modulo(400) == 0) 

Python:

year = int (date[0:4]) is_leap = (year % 4 == 0) and (year % 100 != 0 or year % 400 == 0)

Java:

int year = Integer.valueOf (date.substring (0, 4)).intValue (); boolean is_leap = (year % 4 == 0) && (year % 100 != 0 || year % 400 == 0);

Using leap year tests for year-length calculations

Years are usually 365 days long, but leap years have an extra day. To determine the length of a year in which a date falls, you can use one of the leap year tests just shown to figure out whether to add a day:

$year = substr ($date, 0, 4); $is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0); $days_in_year = ($is_leap ? 366 : 365); 

To compute a year's length in SQL, compute the date of the last day of the year and pass it to DAYOFYEAR⁠(⁠ ⁠ ⁠):

mysql> SET @d = '2006-04-13'; mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')); +---------------------------------------+ | DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) | +---------------------------------------+ |                                   365 | +---------------------------------------+ mysql> SET @d = '2008-04-13'; mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')); +---------------------------------------+ | DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) | +---------------------------------------+ |                                   366 | +---------------------------------------+ 

Using leap year tests for month-length calculations

In Section 6.13, we discussed how to determine the number of days in a month by using the LAST_DAY⁠(⁠ ⁠ ⁠) function in SQL statements.

Within an API language, you can write a non-SQL-based function that, given an ISO-format date argument, returns the number of days in the month during which the date occurs. This is straightforward except for February, where the function must return 29 or 28 depending on whether the year is a leap year. Here's a Ruby version:

def days_in_month(date)   year = date[0..3].to_i   month = date[5..6].to_i   # month, 1-based   days_in_month = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]   days = days_in_month[month-1]   is_leap = (year.modulo(4) == 0) &&               (year.modulo(100) != 0 || year.modulo(400) == 0)   # add a day for Feb of leap years   days += 1 if month == 2 && is_leap   return days end 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net