Обсуждение: 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);