pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate format problems
6+ messages / 4 participants
[nested] [flat]
* Date format problems
@ 2004-02-16 13:06 Mark Roberts <RoberM1@gosh.nhs.uk>
2004-02-16 15:34 ` Re: Date format problems Tom Lane <tgl@sss.pgh.pa.us>
2004-06-03 12:14 ` Re: Date format problems Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mark Roberts @ 2004-02-16 13:06 UTC (permalink / raw)
To: pgsql-sql
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.
Further info:
DATESTYLE is currently set to European.
db table type is 'timestamptz'
#######################################################################################
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());
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';
*********************************************************************************************************************
Im getting desperate, please help if you can, and thx to those that
replied to my previous mail.
Many Thanks in advance,
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Date format problems
2004-02-16 13:06 Date format problems Mark Roberts <RoberM1@gosh.nhs.uk>
@ 2004-02-16 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-02-16 15:34 UTC (permalink / raw)
To: Mark Roberts <RoberM1@gosh.nhs.uk>; +Cc: pgsql-sql
"Mark Roberts" <RoberM1@gosh.nhs.uk> writes:
> 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.
This is way too vague for anyone to help. What PG version are you
using? What is the actual datatype of the column you're inserting into?
Can you provide a specific example of a misformatted data value?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Date format problems
2004-02-16 13:06 Date format problems Mark Roberts <RoberM1@gosh.nhs.uk>
@ 2004-06-03 12:14 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2004-06-03 12:14 UTC (permalink / raw)
To: Mark Roberts <RoberM1@gosh.nhs.uk>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Date format problems
@ 2004-02-16 15:56 Mark Roberts <RoberM1@gosh.nhs.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Roberts @ 2004-02-16 15:56 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
Sure, sorry; Im using postgres version 7.2.1, and the column data type
is 'timestamptz'
Data examples:
13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or
13/04/02 12:35:27
70% of the time it is inserted in the correct format.
The function shown in previous email is called by a C++ Builder 5
program using the postgresSQL ODBC driver version 7.02.00.05 (Insight
Distribution Systems)
Any other info required ?
Sorry for the stupid questions but im a bit of a n00b, no excuse I
guess, but I just cant figure out whats going on.
Thanks for all your efforts,
Kind Regards,
Mark.
>>> Tom Lane <tgl@sss.pgh.pa.us> 02/16/04 03:34pm >>>
"Mark Roberts" <RoberM1@gosh.nhs.uk> writes:
> 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.
This is way too vague for anyone to help. What PG version are you
using? What is the actual datatype of the column you're inserting
into?
Can you provide a specific example of a misformatted data value?
regards, tom lane
_______________________________________
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Date format problems
@ 2004-02-16 16:30 Tom Lane <tgl@sss.pgh.pa.us>
2004-06-03 12:12 ` Re: Date format problems Stef <svb@ucs.co.za>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-02-16 16:30 UTC (permalink / raw)
To: Mark Roberts <RoberM1@gosh.nhs.uk>; +Cc: pgsql-sql
"Mark Roberts" <RoberM1@gosh.nhs.uk> writes:
> Sure, sorry; Im using postgres version 7.2.1, and the column data type
> is 'timestamptz'
> Data examples:
> 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or
> 13/04/02 12:35:27
> 70% of the time it is inserted in the correct format.
Well, part of your confusion might be due to the assumption that it's an
insertion problem; that's not evident from what you've said. The stored
representation of timestamptz is an absolute time count (seconds before
or after midnight 1/1/2000, IIRC) and there is no possibility of "wrong
field order" there. So your problem is either that the timestamp input
converter interprets an ambiguous string incorrectly, or that the output
formatter is presenting the broken-down time in a format other than the
one you want.
In the particular example you gave (a plpgsql function inserting the
value of now()) into a table), I do not believe that the timestamptz
value returned from now() will ever get converted to textual form at
all, so it's not possible for an input interpretation error to occur.
What will hit disk is exactly the same time count now() gave back.
So my bet at this point is that what you have got is a variation in
output formatting style, and the only possible way for that to happen
is if you're not consistently setting the same DateStyle. In recent
releases you can set DateStyle in postgresql.conf, but I think in 7.2
that did not work and you had to do something else to establish a
system-wide default for DateStyle. Check the manual about runtime
configuration settings and postmaster switches.
If you have some entries in the column that are inserted by means
other than this plpgsql function, then it could be that you've got
input interpretation issues for those entries. Again the answer is
most likely that you're not setting DateStyle consistently.
BTW, DateStyle is really two separate variables, one that controls
output format and one that controls the presumed order of MM,DD,YY
fields when the input is ambiguous. (Ugly, I know ... it got that
way for historical reasons ...) Be sure you are setting both parts.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Date format problems
2004-02-16 16:30 Re: Date format problems Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-03 12:12 ` Stef <svb@ucs.co.za>
0 siblings, 0 replies; 6+ messages in thread
From: Stef @ 2004-06-03 12:12 UTC (permalink / raw)
To: pgsql-sql
Tom Lane mentioned :
=> Check the manual about runtime
=> configuration settings and postmaster switches.
I use this :
export PGDATESTYLE=????
(????=ISO,European)
P.S. Is there something wrong with the date on the postgres mailing list machine?
All my mail from the list arrives with a date of : 16 Feb 2004
and hides the bottom of my e-mail folder.
Cheers
Stef
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-03 12:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-16 13:06 Date format problems Mark Roberts <RoberM1@gosh.nhs.uk>
2004-02-16 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-03 12:14 ` Richard Huxton <dev@archonet.com>
2004-02-16 15:56 Re: Date format problems Mark Roberts <RoberM1@gosh.nhs.uk>
2004-02-16 16:30 Re: Date format problems Tom Lane <tgl@sss.pgh.pa.us>
2004-06-03 12:12 ` Stef <svb@ucs.co.za>
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