pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: TRAYLOR YVETTE <ytraylor@cuoftexas.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Holiday Calculations
Date: Thu, 18 Apr 2002 10:19:05 -0700
Message-ID: <web-1376154@davinci.ethosmedia.com> (raw)
In-Reply-To: <EB17D189B65ED21192610008C7A4757302C90926@msexch01.dtcu.org>

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





Message-ID: <web-1376154@davinci.ethosmedia.com>
Permalink:  ../web-1376154@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-1376154@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, ytraylor@cuoftexas.org
  Subject: Re: SQL Holiday Calculations
  In-Reply-To: <web-1376154@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox