sweet that worked!
thanks
Michael Glaesemann wrote:
> On Dec 4, 2006, at 23:52 , Ronin wrote:
>
> > Hi when I do the following function it fills 2 dates per day from 1970
> > to 2050, except that some months (typical 2 months per year) have 4
> > dates for one day. this is totally freaky.. I wonder if postgresql is
> > tripping over itself making a double entry every now and again.
> >
> > for instance I constantly get the following entries
> >
> > "2006-10-01 00:00:00"
> > "2006-10-01 23:59:59.999"
> > "2006-10-01 00:00:00"
> > "2006-10-01 23:59:59.999"
> >
> > Any ideas?
> >
> > Here the function
> >
> > DECLARE
> > yearcnt integer;
> > monthcnt integer;
> > daycnt integer;
> >
> > BEGIN
> >
> > FOR yearcnt IN 1970..2050 LOOP
> > monthcnt=1;
> > FOR monthcnt IN 1..12 LOOP
> > daycnt = 1;
> > FOR daycnt IN 1..31 LOOP
> > insert into datepool values
> > (to_timestamp(yearcnt||'-'||to_char(monthcnt,'FM00')||'-'||to_char
> > (daycnt,'FM09')||'
> > 00:00:00.000','YYYY MM DD HH24:MI:SS.MS'));
> >
> > insert into datepool values
> > (to_timestamp(yearcnt||'-'||to_char(monthcnt,'FM00')||'-'||to_char
> > (daycnt,'FM09')||'
> > 23:59:59.999','YYYY MM DD HH24:MI:SS.MS'));
> >
> >
> > END LOOP;
> > END LOOP;
> > END LOOP;
> >
> > return;
> >
> > END;
>
>
> I think both Martijn and Csaba have the right idea. Here's an
> alternative that should work around those issues:
>
> create table datepool(pool_ts timestamp primary key);
>
> create function fill_date_range(start_date date, end_date date)
> returns void
> language plpgsql as $func$
> declare
> this_date date;
> begin
> this_date := start_date;
> loop
> insert into datepool(pool_ts) values (this_date);
> insert into datepool(pool_ts) values ((this_date +
> 1)::timestamp - interval '.001 second');
> exit when this_date >= end_date;
> this_date := this_date + 1;
> end loop;
> return;
> end;
> $func$;
>
> select fill_date_range('1970-01-01','2050-12-31');
>
> # select * from datepool where pool_ts >= '2006-10-01' limit 10;
> pool_ts
> -------------------------
> 2006-10-01 00:00:00
> 2006-10-01 23:59:59.999
> 2006-10-02 00:00:00
> 2006-10-02 23:59:59.999
> 2006-10-03 00:00:00
> 2006-10-03 23:59:59.999
> 2006-10-04 00:00:00
> 2006-10-04 23:59:59.999
> 2006-10-05 00:00:00
> 2006-10-05 23:59:59.999
> (10 rows)
>
> Hope that helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/