Re: Date Iteration

Am Mit, 2003-07-09 um 09.56 schrieb Jake Stride:
> I am currently writing a calendar application and need to figure out on
> which days recurring events occur. I basically have a table with start
> and end dates and a recurrence type (eg/ day/week/month plus a few
> others).
>
> Therefore I would like to be able to work out (for example) the date of
> every monday between the start and end dates or every 25th day of the
> month etc. Is there an (easy) way to do this, or do I need to write a
> function to do it?

If the number of days between the start and end doesnt become too big
you could create a helper table that just consists of a sequence.

helper_table
1
2
3
4
5
...
1000

Then you can use this table in the FROM list (without connecting the
helper_columns to any other column).
Then you need to specify "WHERE date_start+helper_column<date_end"
Maybe the sequence should start by 0 or the < should be a <=
But I think the idea is clear.

After that you can use the functions:

Day Of Week: date_part('dow', date_start+helper_column)
Day        : date_part('dow', date_start+helper_column)

To calculate some things or restrict the where clause further more.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de