Thread: Date Iteration

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?

Thanks

Jake

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

Well...
Something like 'same day next week'  (now () + '1 weeks') or 'same date
next month' (now () + '1 months') etc is simple.
Stuff like 'next Monday' is messier - the simplest thing I could think of is

now () + '1 weeks'    - (date_part ('dow', now()) || ' days') + '1 days'
::interval  (for next Monday for example) or
now () + '1 months'  - (date_part ('day', now())  || 'days')  + '25
days'::interval  (for the 25th next month)

etc.

Something like crond is much better suited for caledars then plain old
sql :-)

Dima

Jake Stride wrote:

>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?
>
>Thanks
>
>Jake
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>