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 ; 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 ; 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 ; 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 ; Mon, 25 Aug 2003 13:18:32 -0500 (CDT) Message-ID: <006801c36b35$524ecac0$4f0da18e@cleartag> From: "George Weaver" To: 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

Hi Everyone,

I have a situation where two tables have a= =20 "Created" field defined as follows:

table=20 seedlot &n= bsp; "created timestamptz DEFAULT=20 now(), "

table transaction &n= bsp;"created =20 timestamptz NOT NULL DEFAULT now(),"

The transaction table records when a seedl= ot record=20 is created, with both events happening in the same plpgsql= =20 function. The seedlot record is created first, with the transaction t= able=20 being updated later in the procedure.

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

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

base=3D# select created from transaction w= here=20 syslotid=3D153 and=20 transactiontypeid=3D22;
=20 created
------------------------
2003-08-11 12:13:39-05
(1= =20 row)

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 while the transaction wa= s=20 stamped correctly as Aug 11, 2003?

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

However! Two of the tables define cre= ated=20 thus:

"created timestamptz&nbs= p; =20 DEFAULT now() NOT NULL,"

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

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

I am using version 7.3.2.

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

Thanks for your help,

George

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