Thread: CURRENT_TIME
Hi I'm upgrading´a production Data Base from 7.1.3 to 7.2.3 under freeBSD well, the problem is that now in this release CURRENT_TIME return high precision time, is it possible to have only HH:MM:SS its for backguards compatibility. I've tried CURRENT_TIME(0) but it don't work in a function : CREATE FUNCTION "set_fetxa_mod_entitat" () RETURNS opaque AS ' DECLARE idusuari integer; BEGIN SELECT INTO idusuari idoperador from operadors WHERE nomoperador = (CURRENT_USER)::varchar; NEW.horaultimamodificacio = CURRENT_TIME(0); NEW.fetxaultimamodificacio = CURRENT_DATE; NEW.idoperador = idusuari; IF NEW.fetxaultimamodificacio <> OLD.fetxaultimamodificacio THEN NEW.numeromodificacions = OLD.numeromodificacions + 1; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; And the error is : postext=# update finques set idoperador=29 where idfinca=22; NOTICE: Error occurred while executing PL/pgSQL function set_fetxa_mod_finca NOTICE: line 6 at assignment ERROR: Bad time external representation '12:46:25+01' I need only 12:46:25 Thanks a lot !
=?iso-8859-1?Q?Sime=F3_Reig?= <simeo@incofisa.com> writes: > NEW.horaultimamodificacio = CURRENT_TIME(0); > NOTICE: Error occurred while executing PL/pgSQL function > set_fetxa_mod_finca > NOTICE: line 6 at assignment > ERROR: Bad time external representation '12:46:25+01' I think it will work if you insert an explicit cast: NEW.horaultimamodificacio = CURRENT_TIME(0)::time; (or use CAST() syntax if you prefer). In the long run, seems like it would be a good idea for type TIME WITHOUT TIME ZONE's input converter to accept and ignore a timezone field, just as type TIMESTAMP WITHOUT TIME ZONE does: regression=# select '2002-11-06 09:48:40.824687-05'::timestamp; timestamp ---------------------------- 2002-11-06 09:48:40.824687 (1 row) regression=# select '09:48:40.824687-05'::time; ERROR: Bad time external representation '09:48:40.824687-05' Thomas, what do you think --- was this behavior deliberate or an oversight? regards, tom lane
One thing I don't understand is why you don't maintained CURRENT_TIME like 7.1.X and CURRENT_TIME(N) with new behavior,for back compatibility :) Well, seems like it don't work is there any way to do it ? I need only HH:MM:SS Thanks a lot for your time Postgresql under freeBSD (www.freebsd.org) , 103 tables, more than one milion rows, 70 concurrent users via ODBC . In production state since april. Great ! Simeó Reig ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Simeó Reig" <simeo@incofisa.com> Cc: <pgsql-general@postgresql.org>; "Thomas Lockhart" <lockhart@fourpalms.org> Sent: Wednesday, November 06, 2002 3:51 PM Subject: Re: [GENERAL] CURRENT_TIME > =?iso-8859-1?Q?Sime=F3_Reig?= <simeo@incofisa.com> writes: > > NEW.horaultimamodificacio = CURRENT_TIME(0); > > > NOTICE: Error occurred while executing PL/pgSQL function > > set_fetxa_mod_finca > > NOTICE: line 6 at assignment > > ERROR: Bad time external representation '12:46:25+01' > > I think it will work if you insert an explicit cast: > > NEW.horaultimamodificacio = CURRENT_TIME(0)::time; > > (or use CAST() syntax if you prefer). > > In the long run, seems like it would be a good idea for type TIME > WITHOUT TIME ZONE's input converter to accept and ignore a timezone > field, just as type TIMESTAMP WITHOUT TIME ZONE does: > > regression=# select '2002-11-06 09:48:40.824687-05'::timestamp; > timestamp > ---------------------------- > 2002-11-06 09:48:40.824687 > (1 row) > > regression=# select '09:48:40.824687-05'::time; > ERROR: Bad time external representation '09:48:40.824687-05' > > Thomas, what do you think --- was this behavior deliberate or an > oversight? > > regards, tom lane
... > In the long run, seems like it would be a good idea for type TIME > WITHOUT TIME ZONE's input converter to accept and ignore a timezone > field, just as type TIMESTAMP WITHOUT TIME ZONE does: ... > Thomas, what do you think --- was this behavior deliberate or an > oversight? The behavior was deliberate, but predates the implementation of TIMESTAMP WITHOUT TIME ZONE. The time zone is already ignored when converting directly from TIME WITH TIME ZONE to TIME WITHOUT TIME ZONE: lockhart=# select cast(time with time zone '2002-11-06 22:25:57.796141-05' as time); time ----------------- 22:25:57.796141 and one could claim that this should be allowed from string constants too: thomas=# select cast('2002-11-06 22:25:57.796141-05' as time); time ----------------- 22:25:57.796141 Patch included to allow this latter case... - Thomas Index: date.c =================================================================== RCS file: /home/thomas/cvs/repository/pgsql-server/src/backend/utils/adt/date.c,v retrieving revision 1.73 diff -c -r1.73 date.c *** date.c 21 Sep 2002 19:52:41 -0000 1.73 --- date.c 7 Nov 2002 06:32:05 -0000 *************** *** 511,516 **** --- 511,517 ---- fsec_t fsec; struct tm tt, *tm = &tt; + int tz; int nf; char lowstr[MAXDATELEN + 1]; char *field[MAXDATEFIELDS]; *************** *** 521,527 **** elog(ERROR, "Bad time external representation (too long) '%s'", str); if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0) ! || (DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, NULL) != 0)) elog(ERROR, "Bad time external representation '%s'", str); tm2time(tm, fsec, &result); --- 522,528 ---- elog(ERROR, "Bad time external representation (too long) '%s'", str); if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0) ! || (DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz) != 0)) elog(ERROR, "Bad time external representation '%s'", str); tm2time(tm, fsec, &result);