Обсуждение: Migrating from 7.1 to 7.2
Hello, I wrote a plpgsql procedure that returns the executition time of it, like this: create procedure myProc() returns time as ' declare v_start time; v_end time; begin select timeofday()::timestamp::time into v_start; (do some work) select timeofday()::timestamp::time intov_end; return v_end - v_start; end;' language 'plpgsql' This worked fine at 7.1, but now, in 7.2.1, isn't working. In 7.1, i got: template1=# select timeofday()::time; ERROR: Bad time external representation 'Wed May 08 17:19:15.389569 2002 BRT' template1=# select timeofday()::timestamp::time; ?column? ---------- 17:19:20 (1 row) In 7.2.1, i got: bench=# select timeofday()::time; ERROR: Bad time external representation 'Wed May 08 17:20:37.891559 2002 BRT' bench=# select timeofday()::timestamp::time; ERROR: Cannot cast type 'timestamp with time zone' to 'time without time zone' I tried change time zones, date style, etc... but I didn't fix it. Thanks for any help, Edipo Elder [edipoelder@ig.com.br] _________________________________________________________________________ Voc� podia estar baixando sua musica predileta, enquanto lia esse e-mail. N�o perca tempo, tenha acesso r�pido a internet com o Super iG. http://registro.ig.com.br/superig
How about if you replace ::timestamp:: with ::timestamp(0):: ? Timestamps in 7.2 have millisecond information included. Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Edipo Elder > Fernandes de Melo > Sent: Thursday, 9 May 2002 4:49 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] Migrating from 7.1 to 7.2 > > > Hello, > > I wrote a plpgsql procedure that returns the executition time of it, > like this: > > create procedure myProc() returns time as ' > declare > v_start time; > v_end time; > begin > select timeofday()::timestamp::time into v_start; > (do some work) > select timeofday()::timestamp::time into v_end; > return v_end - v_start; > end;' language 'plpgsql' > > This worked fine at 7.1, but now, in 7.2.1, isn't working. In 7.1, i > got: > > template1=# select timeofday()::time; > ERROR: Bad time external representation 'Wed May 08 17:19:15.389569 2002 > BRT' > template1=# select timeofday()::timestamp::time; > ?column? > ---------- > 17:19:20 > (1 row) > > In 7.2.1, i got: > > bench=# select timeofday()::time; > ERROR: Bad time external representation 'Wed May 08 17:20:37.891559 2002 > BRT' > bench=# select timeofday()::timestamp::time; > ERROR: Cannot cast type 'timestamp with time zone' to 'time without time > zone' > > I tried change time zones, date style, etc... but I didn't fix it. > > Thanks for any help, > > Edipo Elder > [edipoelder@ig.com.br] > > _________________________________________________________________________ > Você podia estar baixando sua musica predileta, enquanto lia esse e-mail. > Não perca tempo, tenha acesso rápido a internet com o Super iG. > http://registro.ig.com.br/superig > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Why do I get this message about 100 times ? W On 08.05.2002 17:48:34 pgsql-sql-owner wrote: >Hello, > >I wrote a plpgsql procedure that returns the executition time of it, >like this: > >create procedure myProc() returns time as ' >declare >v_start time; >v_end time; >begin >select timeofday()::timestamp::time into v_start; >(do some work) >select timeofday()::timestamp::time into v_end; >return v_end - v_start; >end;' language 'plpgsql' > >This worked fine at 7.1, but now, in 7.2.1, isn't working. In 7.1, i >got: > >template1=# select timeofday()::time; >ERROR: Bad time external representation 'Wed May 08 17:19:15.389569 2002 >BRT' >template1=# select timeofday()::timestamp::time; >?column? >---------- >17:19:20 >(1 row) > >In 7.2.1, i got: > >bench=# select timeofday()::time; >ERROR: Bad time external representation 'Wed May 08 17:20:37.891559 2002 >BRT' >bench=# select timeofday()::timestamp::time; >ERROR: Cannot cast type 'timestamp with time zone' to 'time without time >zone' > >I tried change time zones, date style, etc... but I didn't fix it. > >Thanks for any help, > >Edipo Elder >[edipoelder@ig.com.br] > >_________________________________________________________________________ >Você podia estar baixando sua musica predileta, enquanto lia esse e-mail. >Não perca tempo, tenha acesso rápido a internet com o Super iG. >http://registro.ig.com.br/superig > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
OK, how about this: create or replace function myProc() returns interval as ' declare v_start timestamp; v_end timestamp; begin select current_timestamp into v_start; -- (do some work) select current_timestamp into v_end; return v_end - v_start; end;' language 'plpgsql'; This will also give you milliseconds. I'm not really sure why the old one worked, but it was bad coding anyway - the above is a lot better, and more SQL-standard. Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of > Wolfgang.Fuertbauer@ebewe.com > Sent: Monday, 13 May 2002 3:13 PM > To: Edipo Elder Fernandes de Melo > Cc: pgsql-sql@postgresql.org; pgsql-sql-owner@postgresql.org > Subject: Re: [SQL] Migrating from 7.1 to 7.2 > > > > Why do I get this message about 100 times ? > > W > On 08.05.2002 17:48:34 pgsql-sql-owner wrote: > >Hello, > > > >I wrote a plpgsql procedure that returns the executition time of it, > >like this: > > > >create procedure myProc() returns time as ' > >declare > >v_start time; > >v_end time; > >begin > >select timeofday()::timestamp::time into v_start; > >(do some work) > >select timeofday()::timestamp::time into v_end; > >return v_end - v_start; > >end;' language 'plpgsql' > > > >This worked fine at 7.1, but now, in 7.2.1, isn't working. In 7.1, i > >got: > > > >template1=# select timeofday()::time; > >ERROR: Bad time external representation 'Wed May 08 17:19:15.389569 2002 > >BRT' > >template1=# select timeofday()::timestamp::time; > >?column? > >---------- > >17:19:20 > >(1 row) > > > >In 7.2.1, i got: > > > >bench=# select timeofday()::time; > >ERROR: Bad time external representation 'Wed May 08 17:20:37.891559 2002 > >BRT' > >bench=# select timeofday()::timestamp::time; > >ERROR: Cannot cast type 'timestamp with time zone' to 'time without time > >zone' > > > >I tried change time zones, date style, etc... but I didn't fix it. > > > >Thanks for any help, > > > >Edipo Elder > >[edipoelder@ig.com.br] > > > >_________________________________________________________________________ > >Você podia estar baixando sua musica predileta, enquanto lia esse e-mail. > >Não perca tempo, tenha acesso rápido a internet com o Super iG. > >http://registro.ig.com.br/superig > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >