Re: Temporal Questions

I've started a thread on the pgsql-general list that has generated a lot
of heat but little light. Since most of you work daily with postgres in
business applications, perhaps you can illuminate the subject.

The application we're writing depends heavily on temporal checks; that is,
whether actions (such as compliance monitoring) are done -- and entered in
the system -- when they're supposed to be. Some permits require monitoring
once per shift, others daily, weekly, or quarterly. Compliance reports must
be submitted quarterly and annually. And renewals must be initiated well in
advance of expiration dates.

With all this date checking to be done, it seems to me that having a
calendar helper table would make the application easier to code, maintain,
and expand. The calendar would (for each date between the start and end
dates; the latter say 30 years from now) store if it's a weekday, weekend
day, holiday, its Julianized date, and whatever else might be useful. This
would, I believe, facilitate queries such as events due in the next week,
fortnight, or month; test if required monitoring results were entered;
and allow sufficient lead time for report and renewal application
preparation knowing that processing requires at least a specified number of
work days.

Seems to me that business financial applications need such a table, too.
For example, stock trades that must be closed within three days of the
transaction, but which cannot be closed on weekends or holidays.

Are my assumptions about the value of such a table invalid? Do you know of
any such calendar table in PostgreSQL SQL (rather than the MS SQL or Oracle
that I find on the Web)?

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Mon, Apr 30, 2007 at 09:51:58AM -0700, Rich Shepard wrote:
> I've started a thread on the pgsql-general list that has generated a lot
> of heat but little light. Since most of you work daily with postgres in
> business applications, perhaps you can illuminate the subject.
<snip: table of holidays for calculating
> Are my assumptions about the value of such a table invalid? Do you know of
> any such calendar table in PostgreSQL SQL (rather than the MS SQL or Oracle
> that I find on the Web)?

I can see why that might generate some heat.

PGSQL certainly has functions that can tell you what day of the week or
year it is. As for holidays, which ones apply? I think it'd be
wonderful if someone maintained tables of gov't holidays for the US,
Canada, the EU, Japan, all 50 States and all 13 Territories and
Provinces, not to mention banks, major business and trading entities.

It'd be a lot of work, with little if any compensation, that would
entangle one in plenty of lawsuits over fines and enforcement actions.

There's some discussion of business day calculations about 1/3 down this
page: http://openacs.org/forums/message-view?message_id=25521
(found with http://www.google.com/search?q=postgres+holiday+table )

My own take? Let the customer figure out their own dang holidays; just
make it easy to enter them in. Heck, a bit of Perl tomfoolery could let
one import iCal files. If they screw up, it's Not Your Fault.

On Apr 30, 2007, at 11:26 AM, Aaron Burt wrote:
> PGSQL certainly has functions that can tell you what day of the
> week or
> year it is. As for holidays, which ones apply? I think it'd be
> wonderful if someone maintained tables of gov't holidays for the US,
> Canada, the EU, Japan, all 50 States and all 13 Territories and
> Provinces, not to mention banks, major business and trading entities.

> My own take? Let the customer figure out their own dang holidays;
> just
> make it easy to enter them in. Heck, a bit of Perl tomfoolery
> could let
> one import iCal files. If they screw up, it's Not Your Fault.

Apple maintains a pretty good list of holidays in iCal format
(online, that is, not local for the iCal application); that'd be a
good place to start. Placing that into a table could result in some
useful checks to handle these cases.

I'd be very wary of using a table for weekend/weekday/ (and
especially)holiday. I would probably put that into an application
that would run at the end of the day, or week, or whatever. Python or
perl's libraries are likely to handle it better than a pgsql.

Adam

On Mon, 30 Apr 2007, Aaron Burt wrote:

> PGSQL certainly has functions that can tell you what day of the week or
> year it is. As for holidays, which ones apply? I think it'd be wonderful
> if someone maintained tables of gov't holidays for the US, Canada, the EU,
> Japan, all 50 States and all 13 Territories and Provinces, not to mention
> banks, major business and trading entities.

Governments take holidays on the slightest excuse. Having them in a table
is not necessary for our application. I think that having a table of
Julianized dates for the current and next few years would make temporal
calculations easier.

One of the major queries to be run is identifying renewal and reporting
dates in the next defined period (fortnight or month, most likely). That
means comparing an issue date and duration (or other regularly scheduled
period) with the calendar date two weeks or a month in advance. This
temporal math is a new venture for me so it will take a while to learn the
most effective ways of getting the needed results.

> My own take? Let the customer figure out their own dang holidays; just
> make it easy to enter them in.

The only concern with holidays is that's when govenment agencies are
closed. But, _our_ clients are smart enough to work around all this on their
own. :-) Heck, that's what they've been doing all along.

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, May 03, 2007 at 08:37:39AM -0700, Rich Shepard wrote:
> On Mon, 30 Apr 2007, Aaron Burt wrote:
>
> >PGSQL certainly has functions that can tell you what day of the week or
> >year it is. As for holidays, which ones apply? I think it'd be wonderful
> >if someone maintained tables of gov't holidays for the US, Canada, the EU,
> >Japan, all 50 States and all 13 Territories and Provinces, not to mention
> >banks, major business and trading entities.
>
> Governments take holidays on the slightest excuse. Having them in a table
> is not necessary for our application. I think that having a table of
> Julianized dates for the current and next few years would make temporal
> calculations easier.

Yes, I got that the first time. Which ones apply, though? If a client
misses a deadline by 1 day because they're dealing with a state that
doesn't recognize Martin Luther King Day, YOU are in trouble, unless you
place the onus on THEM to get it right.

> One of the major queries to be run is identifying renewal and reporting
> dates in the next defined period (fortnight or month, most likely). That
> means comparing an issue date and duration (or other regularly scheduled
> period) with the calendar date two weeks or a month in advance. This
> temporal math is a new venture for me so it will take a while to learn the
> most effective ways of getting the needed results.

Did you look at the link I Googled for you and gave?

> >My own take? Let the customer figure out their own dang holidays; just
> >make it easy to enter them in.
>
> The only concern with holidays is that's when govenment agencies are
> closed. But, _our_ clients are smart enough to work around all this on their
> own. :-) Heck, that's what they've been doing all along.

My point exactly. You don't want the liability for missed deadlines.

On Thu, 3 May 2007, Aaron Burt wrote:

> Yes, I got that the first time. Which ones apply, though? If a client
> misses a deadline by 1 day because they're dealing with a state that
> doesn't recognize Martin Luther King Day, YOU are in trouble, unless you
> place the onus on THEM to get it right.

First, each installation is customized to a specific set of regulatory
requirements. Even within the same company, mines/mills processing different
minerals have different requirements.

Second, if a deadline is missed by one day it's usually no big deal.
Agency staff are so far behind as it is that no one gets cut off at the
knees for missing the deadline. However, potential hassles are avoided by
getting renewals in early.

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863