Hi im using the function below to insert data into my db; im using
now() to get the timestamptz, however when inserted in the db the format
seems to vary, the majority of the time its in the required European
style but does spontaniously change to various other type can anyone
throw any light on this problem.
Not sure what you mean here. Are you saying if you insert 5 timestamps in a row, the third comes out wrong, or that all from one client are wrong, or what?
Further info:
DATESTYLE is currently set to European. db table type is 'timestamptz'
OK - first inspections seem OK. Are you sure no clients have the wrong datestyle set?
#######################################################################################
CREATE FUNCTION newmess(int4, text, varchar) RETURNS varchar AS ' DECLARE userid ALIAS for $1; message ALIAS for $2; touser ALIAS for $3; enttime DATETIME; touserid INTEGER; rdset BIT; from VARCHAR;
BEGIN rdset = 0; touserid=(select id from users where lastname=touser); enttime=(select now());
Easier to say:
enttime := now();
Or, just to use now() in the query below and get rid of the variable altogether.
from=(select lastname from users where id = userid); INSERT INTO CallLog.message(message, fromuser, touser, txtime, rd, fromusern) values(message. userid, touserid, enttime, rdset, from); END; ' LANGUAGE 'plpgsql';
Well, assuming this works at all (and I'm assuming you retyped it since there are some obvious syntax errors), it shouldn't be possible to insert the wrong timestamp. Whether European/American or Klingon format, now() is the current time at the server.
Can you show a sample select where the timestamp is incorrect, but the ones either side are fine? I assume there is some serial message_id column that should act as a clue.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
