I have a table with these columns:
order_date date DEFAULT CURRENT_DATE
order_time time with time zone DEFAULT CURRENT_TIME
setup_date date
last_update date DEFAULT CURRENT_DATE
The order_date and last_update should always be identical because I let
pgsql fill in those fields when a new row is added. Additionally,
setup_date should be identical as well, because my application just
queries the server time (same exact server as pgsql is running on).
However today I noticed something strange: a row was added with these
values:
order_date | order_time | setup_date | last_update
-----------+--------------------+------------+------------
2003-05-26 | 02:22:00.166015-04 | 2003-05-28 | 2003-05-26
Which is very odd because a few minutes later I ran a manual query that
returned this:
dev=> SELECT current_date, current_time;
date | timetz
-----------+--------------------
2003-05-28 | 13:19:39.189404-04
I also checked my apache log files to make sure that the server hadn't
skipped a few days for some reason... But that wasn't the case, and my
logs show hits for the 26th, 27th and 28th, as it should be.
I then went back to my application and made it create a new record. The
following row was created:
order_date | order_time | setup_date | last_update
-----------+--------------------+------------+------------
2003-05-28 | 13:25:12.126979-04 | 2003-05-28 | 2003-05-28
What you may find interesting is that my DB had been mostly dormant for
the past several days. In other words, only a few SELECT queries had
been executed, and no INSERT, UPDATE, DELETE or VACUUM operations had
been run. I have no idea if this is significant or not...
My environment is:
pgsql 7.3.2
Debian/Linux 3.0 (i386)
/etc/timezone is 'US/Eastern'
libdbi-perl 1.21-2
libdbd-pg-perl 1.01-3
No defaults in postgresql.conf were changed except for
'unix_socket_directory'. The Locale is set to 'C'.
I noticed that there are several entries in the HISTORY file for pgsql
7.3.3 that deal with dates and times. Would upgrading fix my problem,
or is this something entirely different?