From georgew1@mts.net Mon Aug  25 18:19:05 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 14FCED1B52C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 15:19:05 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 97855-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 15:18:35 -0300 (ADT)
Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113])
	by svr1.postgresql.org (Postfix) with ESMTP id 94239D1B8D4
	for <pgsql-sql@postgresql.org>; Mon, 25 Aug 2003 15:18:33 -0300 (ADT)
Received: from cleartag (wnpgmb01bbw-ac01-p9-30.mts.net [142.161.9.30])
	by smtp2.mts.net (8.12.8p1/8.11.3) with SMTP id h7PIIVB2012836
	for <pgsql-sql@postgresql.org>; Mon, 25 Aug 2003 13:18:32 -0500 (CDT)
Message-ID: <006801c36b35$524ecac0$4f0da18e@cleartag>
From: "George Weaver" <georgew1@mts.net>
To: <pgsql-sql@postgresql.org>
Subject: Strange behavior with timestamptz
Date: Mon, 25 Aug 2003 13:18:03 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0065_01C36B0B.50642AE0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/295
X-Sequence-Number: 14911

This is a multi-part message in MIME format.

------=_NextPart_000_0065_01C36B0B.50642AE0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Everyone,

I have a situation where two tables have a "Created" field defined as follo=
ws:

table seedlot             "created  timestamptz  DEFAULT now(), "

table transaction        "created  timestamptz  NOT NULL  DEFAULT now(),"

The transaction table records when a seedlot record is created, with both e=
vents happening in the same plpgsql function.  The seedlot record is create=
d first, with the transaction table being updated later in the procedure.

Seedlot 153 was received on August 11 with the following result:

base=3D# select created from seedlot where syslotid=3D153;
        created
------------------------
 2003-11-08 12:13:39-06
(1 row)

base=3D# select created from transaction where syslotid=3D153 and transacti=
ontypeid=3D22;
        created
------------------------
 2003-08-11 12:13:39-05
(1 row)

Does anyone have any idea why the default for seedlot recorded the time wit=
h the day and month switched, resulting in the seedlot record being stamped=
 Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003?

In the same function a number of other records are created and timestamped.=
  In all cases where the table definition statement does not include NOT NU=
LL, the time was stamped with the day and month reversed.

However! Two of the tables define created thus:

  "created  timestamptz   DEFAULT now() NOT NULL,"

but still ended up with the day and month being switched.

The transaction record is the last one in the procedure to be created.

I am using version 7.3.2.

I hope I'm not missing something obvious...

Thanks for your help,
George
------=_NextPart_000_0065_01C36B0B.50642AE0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Everyone,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a situation where two tables have a=
=20
"Created" field defined as follows:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>table=20
seedlot&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;"created&nbsp;&nbsp;timestamptz&nbsp;&nbsp;DEFAULT=20
now(), "</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>table&nbsp;transaction&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;"created&nbsp;=20
timestamptz&nbsp; NOT NULL&nbsp;&nbsp;DEFAULT now(),"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The transaction table records when a seedl=
ot record=20
is created, with both&nbsp;events&nbsp;happening in&nbsp;the same plpgsql=
=20
function.&nbsp; The seedlot record is created first, with the transaction t=
able=20
being updated later in the procedure.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Seedlot 153 was received on August&nbsp;11=
 with the=20
following result:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>base=3D# select created from seedlot where=
=20
syslotid=3D153;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
created<BR>------------------------<BR>&nbsp;2003-11-08 12:13:39-06<BR>(1=
=20
row)</FONT></DIV></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>base=3D# select created from transaction w=
here=20
syslotid=3D153 and=20
transactiontypeid=3D22;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
created<BR>------------------------<BR>&nbsp;2003-08-11 12:13:39-05<BR>(1=
=20
row)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Does anyone have any idea why the default =
for=20
seedlot recorded the time with the day and month switched, resulting in the=
=20
seedlot record being stamped Nov 8, 2003&nbsp;while the transaction&nbsp;wa=
s=20
stamped correctly as Aug 11, 2003?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In the same function a number of other rec=
ords are=20
created and timestamped.&nbsp; In all cases&nbsp;where the table definition=
=20
statement does not include NOT NULL, the time was stamped with the day and =
month=20
reversed.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>However!&nbsp;Two of the tables define cre=
ated=20
thus:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; "created&nbsp;&nbsp;timestamptz&nbs=
p;&nbsp;=20
DEFAULT now() NOT NULL,"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>but still ended up with the day and month =
being=20
switched.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The transaction record is the last one in =
the=20
procedure to be created.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I am using version 7.3.2.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I hope I'm not missing something=20
obvious...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for your help,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>George</FONT></DIV></BODY></HTML>

------=_NextPart_000_0065_01C36B0B.50642AE0--




From tgl@sss.pgh.pa.us Mon Aug  25 21:03:59 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id EE3CFD1B89D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 18:03:59 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 37144-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 18:03:28 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id A7D2CD1B550
	for <pgsql-sql@postgresql.org>; Mon, 25 Aug 2003 18:03:25 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7PKxd5H011000;
	Mon, 25 Aug 2003 16:59:39 -0400 (EDT)
To: "George Weaver" <georgew1@mts.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strange behavior with timestamptz 
In-reply-to: <006801c36b35$524ecac0$4f0da18e@cleartag> 
References: <006801c36b35$524ecac0$4f0da18e@cleartag>
Comments: In-reply-to "George Weaver" <georgew1@mts.net>
	message dated "Mon, 25 Aug 2003 13:18:03 -0500"
Date: Mon, 25 Aug 2003 16:59:39 -0400
Message-ID: <10999.1061845179@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/301
X-Sequence-Number: 14917

"George Weaver" <georgew1@mts.net> writes:
> Does anyone have any idea why the default for seedlot recorded the time wit=
> h the day and month switched, resulting in the seedlot record being stamped=
>  Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003?

It's really not possible to believe that both of those were loaded from
the defaults you show.  now() doesn't ever break down the system clock
value into day/month/year --- it just takes the system clock time in
seconds-since-epoch and adds a constant to get the right zero offset.
So there's no credible mechanism for now() to make such a mistake.

I think that your client software supplied a value for one field and
didn't supply a value for the other, and the supplied value was provided
in the wrong DateStyle.

			regards, tom lane



From georgew1@mts.net Tue Aug  26 02:53:27 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id CEDDDD1B8D1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 23:53:27 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 25500-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Aug 2003 23:52:59 -0300 (ADT)
Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113])
	by svr1.postgresql.org (Postfix) with ESMTP id 8B38DD1B8C6
	for <pgsql-sql@postgresql.org>; Mon, 25 Aug 2003 23:52:37 -0300 (ADT)
Received: from cleartag (wnpgmb01ba1-ac05-p15-149.mts.net [142.161.15.149])
	by smtp2.mts.net (8.12.8p1/8.11.3) with SMTP id h7Q2qcB2015944;
	Mon, 25 Aug 2003 21:52:39 -0500 (CDT)
Message-ID: <001701c36b7d$1d44fb90$950fa18e@cleartag>
From: "George Weaver" <georgew1@mts.net>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <006801c36b35$524ecac0$4f0da18e@cleartag>
	<10999.1061845179@sss.pgh.pa.us>
Subject: Re: Strange behavior with timestamptz
Date: Mon, 25 Aug 2003 21:51:56 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/300
X-Sequence-Number: 14916

Hi Tom,

I have written both the application and the PostgreSQL procedures (still in
development).  All the timestamps I referred to are created by default when
the stored procedure is invoked.  The problem may be due to some
inconsistency in how PostgreSQL is interpreting what the operating system
(Windows XP) is supplying.  While I have not been able to recreate the
problem directly, another aspect of the process does show an inconsistency.

In the application the user enters the date the product was received (which
may be different from the date the record is created).  This is passed to
the stored procedure as a parameter of type date, and is inserted into a
date field (datereceived).  By changing the system date and running the
application the following occurred:

Date received entered as August 11, 2003:

? me.datetimepicker1.value
#8/11/2003 8:18:28 PM#

base=# select datereceived, created from receiving where receivingid=56;
 datereceived |           created
--------------+------------------------------
 2003-11-08   | 2003-08-25 20:20:55.41425-05
(1 row)

Date received entered as August 25, 2003:

? me.datetimepicker1.value
#8/25/2003 8:22:37 PM#

base=# select datereceived, created from receiving where receivingid=57;
 datereceived |           created
--------------+------------------------------
 2003-08-25   | 2003-08-25 20:22:39.68625-05
(1 row)

base=# show datestyle;
               DateStyle
---------------------------------------
 ISO with US (NonEuropean) conventions
(1 row)

In both cases the month is being sent to the stored procedure first, but in
the first instance (month < 13) it is being interpreted as the day.

George

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "George Weaver" <georgew1@mts.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, August 25, 2003 3:59 PM
Subject: Re: [SQL] Strange behavior with timestamptz


> "George Weaver" <georgew1@mts.net> writes:
> > Does anyone have any idea why the default for seedlot recorded the time
wit=
> > h the day and month switched, resulting in the seedlot record being
stamped=
> >  Nov 8, 2003 while the transaction was stamped correctly as Aug 11,
2003?
>
> It's really not possible to believe that both of those were loaded from
> the defaults you show.  now() doesn't ever break down the system clock
> value into day/month/year --- it just takes the system clock time in
> seconds-since-epoch and adds a constant to get the right zero offset.
> So there's no credible mechanism for now() to make such a mistake.
>
> I think that your client software supplied a value for one field and
> didn't supply a value for the other, and the supplied value was provided
> in the wrong DateStyle.
>
> regards, tom lane
>




From georgew1@mts.net Tue Aug  26 12:29:35 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 82791D1D9F6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Aug 2003 09:29:35 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 91343-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Aug 2003 09:29:03 -0300 (ADT)
Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113])
	by svr1.postgresql.org (Postfix) with ESMTP id B90A6D1B8B1
	for <pgsql-sql@postgresql.org>; Tue, 26 Aug 2003 09:28:59 -0300 (ADT)
Received: from cleartag (wnpgmb01bbw-ac01-p8-156.mts.net [142.161.8.156])
	by smtp2.mts.net (8.12.8p1/8.11.3) with SMTP id h7QCT0B2027828;
	Tue, 26 Aug 2003 07:29:01 -0500 (CDT)
Message-ID: <001801c36bcd$a25a7210$9c08a18e@cleartag>
From: "George Weaver" <georgew1@mts.net>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <006801c36b35$524ecac0$4f0da18e@cleartag>
	<10999.1061845179@sss.pgh.pa.us>
	<001701c36b7d$1d44fb90$950fa18e@cleartag>
Subject: Re: Strange behavior with timestamptz
Date: Tue, 26 Aug 2003 07:28:18 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/307
X-Sequence-Number: 14923

Hi Tom,

Not believing that PostgreSQL would be less consistent than Microsoft :-), I
spent some time looking at how the application actually was transferring the
date to the database procedure.  When the datereceived parameter was defined
as type Date, it was actually arriving at the procedure as "11-08-2003" and
"25-08-2003" in spite of showing up as indicated below.  When I redefined
the datereceived parameter as type OdbcDate, it arrived correctly at the
procedure as "2003-08-11" and "2003-08-25".

Checking further, this appears to result from the computer's time settings
(English - Canada) which I know realize use the European format.

So it looks like this problem arose due to my inexperience.

(Nonetheless I am still perplexed by the fact that the default values were
assigned inconsistently as explained earlier and will try to puzzle this one
through as well).

Sorry for the confusion.

George

----- Original Message ----- 
From: "George Weaver" <georgew1@mts.net>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, August 25, 2003 9:51 PM
Subject: Re: [SQL] Strange behavior with timestamptz


> Hi Tom,
>
> I have written both the application and the PostgreSQL procedures (still
in
> development).  All the timestamps I referred to are created by default
when
> the stored procedure is invoked.  The problem may be due to some
> inconsistency in how PostgreSQL is interpreting what the operating system
> (Windows XP) is supplying.  While I have not been able to recreate the
> problem directly, another aspect of the process does show an
inconsistency.
>
> In the application the user enters the date the product was received
(which
> may be different from the date the record is created).  This is passed to
> the stored procedure as a parameter of type date, and is inserted into a
> date field (datereceived).  By changing the system date and running the
> application the following occurred:
>
> Date received entered as August 11, 2003:
>
> ? me.datetimepicker1.value
> #8/11/2003 8:18:28 PM#
>
> base=# select datereceived, created from receiving where receivingid=56;
>  datereceived |           created
> --------------+------------------------------
>  2003-11-08   | 2003-08-25 20:20:55.41425-05
> (1 row)
>
> Date received entered as August 25, 2003:
>
> ? me.datetimepicker1.value
> #8/25/2003 8:22:37 PM#
>
> base=# select datereceived, created from receiving where receivingid=57;
>  datereceived |           created
> --------------+------------------------------
>  2003-08-25   | 2003-08-25 20:22:39.68625-05
> (1 row)
>
> base=# show datestyle;
>                DateStyle
> ---------------------------------------
>  ISO with US (NonEuropean) conventions
> (1 row)
>
> In both cases the month is being sent to the stored procedure first, but
in
> the first instance (month < 13) it is being interpreted as the day.
>
> George
>
> ----- Original Message ----- 
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "George Weaver" <georgew1@mts.net>
> Cc: <pgsql-sql@postgresql.org>
> Sent: Monday, August 25, 2003 3:59 PM
> Subject: Re: [SQL] Strange behavior with timestamptz
>
>
> > "George Weaver" <georgew1@mts.net> writes:
> > > Does anyone have any idea why the default for seedlot recorded the
time
> wit=
> > > h the day and month switched, resulting in the seedlot record being
> stamped=
> > >  Nov 8, 2003 while the transaction was stamped correctly as Aug 11,
> 2003?
> >
> > It's really not possible to believe that both of those were loaded from
> > the defaults you show.  now() doesn't ever break down the system clock
> > value into day/month/year --- it just takes the system clock time in
> > seconds-since-epoch and adds a constant to get the right zero offset.
> > So there's no credible mechanism for now() to make such a mistake.
> >
> > I think that your client software supplied a value for one field and
> > didn't supply a value for the other, and the supplied value was provided
> > in the wrong DateStyle.
> >
> > regards, tom lane
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




From tgl@sss.pgh.pa.us Tue Aug  26 13:09:24 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id CCB97D1B897
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Aug 2003 10:09:24 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 03526-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Aug 2003 10:08:50 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 1C39AD1B56C
	for <pgsql-sql@postgresql.org>; Tue, 26 Aug 2003 10:08:47 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7QD8k5H007689;
	Tue, 26 Aug 2003 09:08:46 -0400 (EDT)
To: "George Weaver" <georgew1@mts.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strange behavior with timestamptz 
In-reply-to: <001801c36bcd$a25a7210$9c08a18e@cleartag> 
References: <006801c36b35$524ecac0$4f0da18e@cleartag>
	<10999.1061845179@sss.pgh.pa.us>
	<001701c36b7d$1d44fb90$950fa18e@cleartag>
	<001801c36bcd$a25a7210$9c08a18e@cleartag>
Comments: In-reply-to "George Weaver" <georgew1@mts.net>
	message dated "Tue, 26 Aug 2003 07:28:18 -0500"
Date: Tue, 26 Aug 2003 09:08:46 -0400
Message-ID: <7688.1061903326@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/306
X-Sequence-Number: 14922

> ... When the datereceived parameter was defined
> as type Date, it was actually arriving at the procedure as "11-08-2003" and
> "25-08-2003" in spite of showing up as indicated below.  When I redefined
> the datereceived parameter as type OdbcDate, it arrived correctly at the
> procedure as "2003-08-11" and "2003-08-25".

Yeah.  The first two formats are ambiguous, in Postgres' mind anyway.
With the US datestyle setting, it will first attempt to parse as mm-dd-yyyy
and if that fails try dd-mm-yyyy.  So you'd need to change the datestyle
to Euro to get dd-mm-yyyy input to be parsed reliably.

As of 7.4 this is being tightened up, btw --- it'll be mm-dd-yyyy or error.

But AFAICS this has nothing to do with a default now() insertion,
because that value is never converted to a string before it gets into
the stored column.

			regards, tom lane