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
Message-ID: <006801c36b35$524ecac0$4f0da18e@cleartag> (raw)
Hi Everyone,
I have a situation where two tables have a "Created" field defined as follows:
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 events happening in the same plpgsql function. The seedlot record is created first, with the transaction table being updated later in the procedure.
Seedlot 153 was received on August 11 with the following result:
base=# select created from seedlot where syslotid=153;
created
------------------------
2003-11-08 12:13:39-06
(1 row)
base=# select created from transaction where syslotid=153 and transactiontypeid=22;
created
------------------------
2003-08-11 12:13:39-05
(1 row)
Does anyone have any idea why the default for seedlot recorded the time with 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 NULL, 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
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
Subject: Re: Strange behavior with timestamptz
In-Reply-To: <006801c36b35$524ecac0$4f0da18e@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