Re: CURRENT_DATE and CURRENT_TIME return incorrect values

On Thu, May 29, 2003 at 07:57:39PM -0500, DeJuan Jackson wrote:
> Did you create the table on 2003-05-26 by any chance?

Nope, that table has been around for about a month or so.  I haven't even
made any recent changes to it.

> 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.

The \d output looks like this:

      Column      |        Type         |                   Modifiers
------------------+---------------------+-----------------------------------------------
 order_date       | date                | default date('now'::text)
 order_time       | time with time zone | default ('now'::text)::time(6) with time zone
 setup_date       | date                |
 last_update      | date                | default date('now'::text)

I think this is correct?  I want pgsql to enter the current date/time in
by default.  It normally seems to work ok, except that one time a couple
days ago when it entered '2003-05-26' instead of '2003-05-28' for some
reason that I don't understand.  Especially considering that it entered
the correct date/time when I added another row just a few minutes later...

It's very strange.  I think it would be difficult to reproduce this
behavior because my DB had been sitting around mostly idle for some
time, and I've never seen this happen before.

I'm fairly certain the problem is related to pgsql because my system
time has been accurate all along.  In other words, the system time
didn't jump from 2003-05-26 to 2003-05-28 in one instant.  My
apache logs show regular hits for that date range, and so do the other
system logs (ie, /var/log/messages has normal, regular entries).

The only thing I can think of is that pgsql cached an old date and time,
for some reason, and used that for the first record, and then actually
queried the system's date/time after that.  But that sounds pretty
weird...

I really need for the order_date to be 100% accurate though, because my
application must do calculations based on that.  So I'm thinking about
having have it grab the date/time directly from the system clock  and
enter it instead of leaving that to pgsql.  Unless anyone can point out
where I made an error.