Re: CURRENT_DATE and CURRENT_TIME return incorrect values

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