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

The big question is: Can you repeat it?

The next big question is: Can you supply a complete example of how to repeat
it.

The only things that crossed my mind were a) you started a transaction on the
26th and only wrote that record on the 28th or b) which ever system you were
querying the time of to get the 28th had a different time to the database
server.

Is there some reason you are splitting the order time and date into two columns
rather than using a single timestamp one?

--
Nigel Andrews



On Fri, 30 May 2003, valerian wrote:

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