Message-ID: <40BF1614.8020404@archonet.com>
Date: Thu, 03 Jun 2004 13:14:12 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
MIME-Version: 1.0
To: Mark Roberts <RoberM1@gosh.nhs.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date format problems
References: <s030c05a.023@smtp.gosh.nhs.uk>
In-Reply-To: <s030c05a.023@smtp.gosh.nhs.uk>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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