Re: Strange behavior with timestamptz - Mailing list pgsql-sql

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
>