Re: Date format problems

"Mark Roberts" <RoberM1(at)gosh(dot)nhs(dot)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