pgsql bug found?

On Mon, Dec 04, 2006 at 06:52:19AM -0800, 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

It's either a wierd daylight savings thing, or something to do with the
fact that not all months have 31 days.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

FOR daycnt IN 1..31 LOOP

How about months with less than 31 days ? What do you get for those if
the day is 31 ?

Cheers,
Csaba.

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

sweet that worked!

thanks

Michael Glaesemann wrote: