Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 16345475452 for ; Thu, 18 Apr 2002 13:19:07 -0400 (EDT) Received: from [209.122.114.82] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1376154; Thu, 18 Apr 2002 10:19:05 -0700 From: "Josh Berkus" Subject: Re: SQL Holiday Calculations To: TRAYLOR YVETTE Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Thu, 18 Apr 2002 10:19:05 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200204/232 X-Sequence-Number: 7496 Yvette, > I just saw an email that you sent a while back: > On Tue, 18 Sep 2001, Josh Berkus wrote: > Folks, > > I'm spec'ing a > calendar > app for PostgreSQL, and was wondering if anyone > had already solved > the > following problem: > > How can I calculate the dates of American > holidays? > > > Obviously, Christmas & New Year's are easy. As is July 4. > > > However, > Thanksgiving is the last Thursday in November, unless the month > > ends on a > Thursday or Friday, in which case it is the next-to-last. > Memorial > Day and > Labor Day are simpler, but also use the "First or Last > Monday in x > month" > idea. > > I was wondering if anyone had already figured out these > calculations, in > any language (SQL would be terrific). > > Thanks! > Can you tell me if you ever received a reply or figured out how to do > the > Calculations in SQL. No, actually, In fact, I got an e-mail from Joe Celko (I think) where he points out that any holiday calculations are undependable because state legislatures and Congress change holiday schedules all the time. In fact, the only reliable guide is an almanac. You could fairly easily calculate, say, "The Second Monday In February" using Postgres' date functions, as: select '2002-02-01'::DATE + (extract(dow from '2002-02-01'::DATE) + 5::INT)::INT; However, President's day is not *always* the second monday of February in all states. I was able to lift a list of the next 5 years of US National holidays from a vendor application. When I get it cleaned up, I'll post it somewhere, probably techdocs. -Josh Berkus