Обсуждение: pgsql bug found?

Поиск
Список
Период
Сортировка

pgsql bug found?

От
"Ronin"
Дата:
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;

Re: pgsql bug found?

От
Martijn van Oosterhout
Дата:
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/
> From each according to his ability. To each according to his ability to litigate.
Вложения

Re: pgsql bug found?

От
Csaba Nagy
Дата:
>             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.

Re: pgsql bug found?

От
Michael Glaesemann
Дата:
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

Re: pgsql bug found?

От
"Ronin"
Дата:
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/