Determination of a leap year in PostgreSQL

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.
About these ads

Nerd.

Tagged with: , , , ,
Posted in Coding
4 comments on “Determination of a leap year in PostgreSQL
  1. Daniel Browning says:

    > 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.

  2. okbobcz says:

    inlined SQL is very very fast – plpgsql almost all expressions translate to some form of “inlined SQL” so cannot be faster than inlined SQL.

  3. Ireneusz Pluta says:

    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$

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: