CURRENT_DATE and CURRENT_TIME return incorrect values - Mailing list pgsql-general

From valerian
Subject CURRENT_DATE and CURRENT_TIME return incorrect values
Date
Msg-id 20030528183610.GA4117@hotpop.com
Whole thread Raw
Responses Re: CURRENT_DATE and CURRENT_TIME return incorrect values  (DeJuan Jackson <djackson@speedfc.com>)
List pgsql-general
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?



pgsql-general by date:

Previous
From: Divya Jain
Date:
Subject: modifying VARCHAR max length
Next
From: Divya Jain
Date:
Subject: modifying VARCHAR max length