Re: Date format problems

From: Richard Huxton <dev(at)archonet(dot)com>
To: Mark Roberts <RoberM1(at)gosh(dot)nhs(dot)uk>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Date format problems
Date: 2004-06-03 12:14:12
Message-ID: 40BF1614.8020404@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2004-06-03 14:24:02 Re: Reference with condition on other table column?
Previous Message Stef 2004-06-03 12:12:27 Re: Date format problems