Re: Date format problems

Mark Roberts wrote:
> 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