Обсуждение: Holiday Calculations?
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!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Вложения
Not that this is the best solution, but I read in a book (SQL for smarties I think) the following (maybe I'm wrong, but this is what I remember :) Create a holidays table. Put in all the holidays. Do the math yourself and just put them in there manually for the next 10 years or so (and then remind yourself to do it again in 10 years :) Then just look them up in that table. The example I was reading about was how to determine "the next 3rd business day". his advice was to put all the weekends and holidays in that table and then it made the queries easy. Something like that anyway :) Check out the book for more info. -philip 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! > > -Josh > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco >
Based on your criteria (I never really had thought about Thanksgiving
before, as far as when it hits), but here is a small Tcl proc that I
think will do it. You may need to add error checking. Takes the year
(i.e. 2001), and returns the day date (i.e. 22).
-------------------------------------------
proc getTG {year} { set di \[clock format [clock scan $year-11-30] -format "%w"] return [expr {30 -
((($di+1)%7)+2)}]
}
---------------------------------------------
I think this will do it (It might not work for esoteric cases...I didn't
check all). Sorry, I don't know SQL well enough to give an answer in
SQL...maybe someone else will though...or maybe you can extrapolate this
proc into SQL.
I think you may be able to modify this for Memorial and Labor day as
well.
--brett
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!
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ------------------------------------------------------------------------
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
I couldn't resist, so I went ahead and did all of them. I might be using
these in a future app anyways. Again these are Tcl procs, but you may be
able to transfer the logical to another language, if you want.
proc getTG {year} { set di \[clock format [clock scan $year-11-30] -format "%w"] return [expr {30 -
((($di+1)%7)+2)}]
}
proc getMem {year} { set di \[clock format [clock scan $year-05-31] -format "%w"] return [expr {31 -
((($di+6)%7))}]
}
proc getLabor {year} { set di \[clock format [clock scan $year-09-01] -format "%w"] return [expr {(((8-$di)%7) +
1)}]
}
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!
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ------------------------------------------------------------------------
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> Name:
> Type: Plain Text (text/plain)
> Encoding: base64
>
> ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Brett Schwarz wrote:
>
> I couldn't resist, so I went ahead and did all of them. I might be using
> these in a future app anyways. Again these are Tcl procs, but you may be
> able to transfer the logical to another language, if you want.
>
if you need this in perl i think Data::Manip might hep:
Date_IsHoliday $name=&Date_IsHoliday($date);
This returns undef if $date is not a holiday, or a string containing the name of the holiday
otherwise. An empty string is returned for an unnamed holiday.
also there is a section on holidays in the
config file where you can set up your own : 1/1 = New Year's Day third Monday
inFeb = Presidents' Day fourth Thu in Nov = Thanksgiving
hope this is useful!
clayton cottingham wrote: > > Brett Schwarz wrote: > > > > I couldn't resist, so I went ahead and did all of them. I might be using > > these in a future app anyways. Again these are Tcl procs, but you may be > > able to transfer the logical to another language, if you want. > > > > if you need this in perl i think Data::Manip might hep: > sorry , i noticed a typo it should be Date::Manip