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
Show quoted text
=?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
Attachments:
date.c.patchtext/plain; name=date.c.patchDownload
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);
Hi,
I don't know if it's important or not ... but on my linux Debian woody, with
fr_FR@euro parameter when I do the initdb I've got a creating template1
database in /usr/local/pgsql/data/base/1... Failed to initialize lc_messages
to ''
I just would like to know what that's mean exactly ...
Exact message of initdb :
ￜ/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale fr_FR@euro.
This locale setting will prevent the use of indexes for pattern matching
operations. ï¿œIf that is a concern, rerun initdb with the collation order
set to "C". ï¿œFor more information see the Administrator's Guide.
Fixing permissions on existing directory /usr/local/pgsql/data... ok
creating directory /usr/local/pgsql/data/base... ok
creating directory /usr/local/pgsql/data/global... ok
creating directory /usr/local/pgsql/data/pg_xlog... ok
creating directory /usr/local/pgsql/data/pg_clog... ok
creating template1 database in /usr/local/pgsql/data/base/1... Failed to
initialize lc_messages to ''
ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. You can now start the database server using:
ￜ ￜ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
or
ￜ ￜ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
regards,
--
Hervￜ Piedvache
Elma Ingï¿œnierie Informatique
6 rue du Faubourg Saint-Honorￜ
F-75008 - Paris - France
Tel. 33-144949901
fax. 33-144949902
=?iso-8859-15?q?Herv=E9=20Piedvache?= <herve@elma.fr> writes:
I don't know if it's important or not ... but on my linux Debian woody, with
fr_FR@euro parameter when I do the initdb I've got a creating template1
database in /usr/local/pgsql/data/base/1... Failed to initialize lc_messages
to ''
I just would like to know what that's mean exactly ...
It means your platform doesn't accept "fr_FR@euro" as a setting for
LC_MESSAGES. Since "fr_FR@euro" is evidently accepted as a setting for
other LC_ variables, this is a bug in the locale definition, which you
should report to the Debian folk.
Until they fix it, you can probably work around it by starting the
postmaster with LC_MESSAGES explicitly set to something different than
LANG/LC_ALL are (maybe plain "fr_FR" would work). I think you will need
to adjust lc_messages in postgresql.conf as well.
regards, tom lane