Thread: timestamptz - problems

timestamptz - problems

From
"Mark Roberts"
Date:
Hi im using 'timestamptz' in a function called: 'getdate' to enter the
start and finish time into a db field, however the date appears to be
inserted into the db in a random format, i wish for it to only be
entered into the db as DD-MM-YYYY (European,UK). I understand this is
probably a very simple problem to resolve but thus far I have failed,
can anyone plz help.

************  'getdate' function is as follows: ***************

CREATE FUNCTION getdate() RETURNS timestamptz AS '
BEGIN
RETURN now();
END; '  LANGUAGE 'plpgsql';

************ Inserted using the following function: *****************

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 getdate());
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';

*********************************************************************************************************************

Kind Regards, Mark.  



_______________________________________
Disclaimer: Great Ormond Street Hospital for Children NHS Trust

SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note
that Internet E-mail is simply not a secure communication medium.
We strongly advise that you understand & observe this lack of security
when e-mailing us.

This email and any files transmitted with it are intended solely for
the use of the individual to whom they are addressed.  If you have
received this email in error please notify your email administrator.

Any views or opinions are solely those of the author of this email
and do not represent those of Great Ormond Street Hospital for
Children NHS Trust unless specifically stated.

VIRUSES: This email message has been checked for the presence of
computer viruses by Sophos antivirus software.  However, this does
not guarantee that this email is free of viruses, and the recipient
should perform their own check. 



Re: timestamptz - problems

From
Christopher Browne
Date:
Oops! RoberM1@gosh.nhs.uk ("Mark Roberts") was seen spray-painting on a wall:
> Hi im using 'timestamptz' in a function called: 'getdate' to enter
> the start and finish time into a db field, however the date appears
> to be inserted into the db in a random format, i wish for it to only
> be entered into the db as DD-MM-YYYY (European,UK). I understand
> this is probably a very simple problem to resolve but thus far I
> have failed, can anyone plz help.

It seems to me that you're asking the wrong question.

The data type you should normally use for handling timestamps is
"timestamptz," which records the date, time and time zone.

It is certainly _NOT_ inserted in a "random format;" there is NO
ambiguity about what the date and time types in PostgreSQL store in
the database.

What _might_ vary is how a date happens to be _displayed_, and that is
something that you might reasonably want to control.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://www3.sympatico.ca/cbbrowne/x.html
This Bloody Century
"Early this century there was a worldwide socialist revolution. The
great battles were then between International Socialism, National
Socialism, and Democratic Socialism. Democratic Socialism won because
the inertia of democracy prevented the socialism from doing as much
damage here. Capitalism first reemerged from the ashes of National
Socialism, in Germany and Japan. It is now reemerging from the ashes
of International Socialism.  Next?

After all, inertia works both ways..."
-- Mark Miller