CURRENT_DATE and CURRENT_TIME return incorrect values

From: valerian <valerian2(at)hotpop(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: CURRENT_DATE and CURRENT_TIME return incorrect values
Date: 2003-05-28 18:36:10
Message-ID: 20030528183610.GA4117@hotpop.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Divya Jain 2003-05-28 18:44:30 modifying VARCHAR max length
Previous Message Divya Jain 2003-05-28 18:35:47 modifying VARCHAR max length