Determination of a leap year in PostgreSQL

Coding 1 Minute

From Wikipedia:

A leap year (or intercalary or bissextile year) is a year containing one extra day (or, in the case of lunisolar calendars, a month) in order to keep the calendar year synchronized with the astronomical or seasonal year. Because seasons and astronomical events do not repeat in a whole number of days, a calendar that had the same number of days in each year would, over time, drift with respect to the event it was supposed to track. By occasionally inserting (or intercalating) an additional day or month into the year, the drift can be corrected. A year that is not a leap year is called a common year.

So to determine whether a year is a leap year or not in either the Gregorian calendar we need to check such condition:

(Year mod 4 = 0) AND ((Year mod 100 != 0) or (Year mod 400 = 0)),
where mod is modulo operation

This dirty query does the trick then:

SELECT c.y, (c.y % 4 = 0) AND ((c.y % 100 <> 0) OR (c.y % 400 = 0))
 FROM generate_series(1582, 2020) AS c(y)

output

I prefer to have function for this task. First I thought it must be written in plpgsql to be fast enough. Yeah, I’m to lazy for C functions. However after tests I saw that SQL function has the same productivity. Don’t know why. Here the sources for both of them:

CREATE OR REPLACE FUNCTION isleapyear(year integer)
 RETURNS boolean AS
'SELECT ($1 % 4 = 0) AND (($1 % 100 <> 0) or ($1 % 400 = 0))'
 LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION isleapyear(year integer)
 RETURNS boolean AS
'BEGIN
  RETURN (Year % 4 = 0) AND ((Year % 100 0) or (Year % 400 = 0));
END'
 LANGUAGE plpgsql IMMUTABLE STRICT;

P.S. Some funny traditions:

  • On the British isles, it is a tradition that women may propose marriage only on leap years.
  • In Denmark, the tradition is that women may propose on the bissextile leap day, February 24, and that refusal must be compensated with 12 pairs of gloves.
  • In Finland, the tradition is that if a man refuses a woman’s proposal on leap day, he should buy her the fabrics for a skirt.

Published by pashagolub

Nerd.

Published

4 thoughts on “Determination of a leap year in PostgreSQL”

  1. > However after tests I saw that SQL function has the same productivity. Don’t know why.

    I’ve always assumed that if the function is simple enough that it can be implemented in vanilla sql, then sql would usually be faster than plpgsql. Particularly if the sql function is inlined and a cached query plan is used. I don’t know if that’s really true as a general rule or not. In the case of your example, you’d have to remove strict to allow inlining.

    Like

    Reply

  3. This algorithm is so well known, but at the same time so difficult to recollect without help of Wikipedia (or K&R C book).

    So, as we already have pgsql handy, why not do it its way?

    CREATE OR REPLACE FUNCTION yaisleapyear(yyyy integer )
    RETURNS boolean
    LANGUAGE plpgsql
    AS $function$
    BEGIN
    BEGIN
    PERFORM ((yyyy)::text || ‘-02-29’)::date;
    EXCEPTION
    WHEN SQLSTATE ‘22008’ THEN
    RETURN FALSE;
    END;
    RETURN TRUE;
    END;
    $function$

    Like

    Reply

Leave a comment