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

Did you create the table on 2003-05-26 by any chance?
It appears that the CURRENT_DATE/CURRENT_TIME in your create table
statement got interpreted, and replaced, so every record that ever gets
inserted will have the same date and time.
use a \d table_name in psql to confirm.
You should be able to alter the table/columns and correct the problem.

valerian wrote:

>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?
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
>