From: Richard Huxton <dev@archonet.com>
To: Mark Roberts <RoberM1@gosh.nhs.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date format problems
Date: Thu, 03 Jun 2004 13:14:12 +0100
Message-ID: <40BF1614.8020404@archonet.com> (raw)
In-Reply-To: <s030c05a.023@smtp.gosh.nhs.uk>
References: <s030c05a.023@smtp.gosh.nhs.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, RoberM1@gosh.nhs.uk
Subject: Re: Date format problems
In-Reply-To: <40BF1614.8020404@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox