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

Holiday discounts on pgmdd & postgresdac

Ded MorozHolidays came suddenly. I know we’re little bit late with Christmas presents, but who cares anyway!

Everybody is welcome to use “Christmas” coupon code till January 14 for 20% discount on any product from MicroOLAP Technologies including the most robust Database Designer for PostgreSQL and PostgresDAC component suite.

From all of us at MicroOLAP, we wish you happy holidays and a prosperous new year!