From: George Weaver <georgew1@mts.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strange behavior with timestamptz
Date: Tue, 26 Aug 2003 07:28:18 -0500
Message-ID: <001801c36bcd$a25a7210$9c08a18e@cleartag> (raw)
References: <006801c36b35$524ecac0$4f0da18e@cleartag>
<10999.1061845179@sss.pgh.pa.us>
<001701c36b7d$1d44fb90$950fa18e@cleartag>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: georgew1@mts.net, tgl@sss.pgh.pa.us
Subject: Re: Strange behavior with timestamptz
In-Reply-To: <001801c36bcd$a25a7210$9c08a18e@cleartag>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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