<john.udick@zionsbancorp.com> wrote:
> PostgreSQL version: 8.4.1
You are missing years of bug fixes. You should be staying more
up-to-date on minor releases.
http://www.postgresql.org/support/versioning/
To review what has been fixed between 8.4.1 and 8.4.10, please look
through the links from 8.4.2 and up on this page and see if any of
them seem to be related:
http://www.postgresql.org/docs/8.4/static/release.html
Or just apply all of those bug fixes and try again.
> I would expect that at the time/date of the of now() and
> clock_timestamp() to be equal;
You would be wrong. The fine manual describes them here:
http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
Picking sentences out of that page:
| now() is a traditional PostgreSQL equivalent to
| transaction_timestamp().
| transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
| named to clearly reflect what it returns.
| clock_timestamp() returns the actual current time, and therefore
| its value changes even within a single SQL command.
now() returns the timestamp for the start of the transaction, while
clock_timestamp() grabs a new value from the system clock for every
execution.
None of that, however, seems to be very directly related to the
issue you describe below.
> [results obtained in some unknown fashion from a table of unknown
> description doesn't have expected dates]
> Why is this not the case? I would expect that my records should
> all have the same value, not some arbitrary date from the past.
You haven't provided near enough information for anyone to hazard a
guess why this might be. Are they time columns instead of
timestamp? Do you have triggers which modify the values? There are
all sorts of things that might be happening that you're not telling
us about.
A self-contained test case on a fairly recent minor release would be
useful, if you can create one. Something like this:
intclient=# create table t (id int primary key, d1 date not null, d2
date not null);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
intclient=# insert into t
values
(1, date_trunc('hours',now()), current_date),
(2, localtimestamp::date, current_date),
(3, now(), clock_timestamp()::DATE);
INSERT 0 3
intclient=# select * from t;
id | d1 | d2
----+------------+------------
1 | 2012-01-11 | 2012-01-11
2 | 2012-01-11 | 2012-01-11
3 | 2012-01-11 | 2012-01-11
(3 rows)
You might find this page helpful:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
-Kevin
