Re: Bad timestamp external representation

On Tue, 29 Apr 2003, Mark Tessier wrote:

> On Tue, 29 Apr 2003 00:02:20 -0400
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Mark Tessier <m_tessier(at)sympatico(dot)ca> writes:
> > > Actually, that was the last thing I tried before I wrote this note. Before I entered
> >
> > > herboris=> INSERT INTO cart (cartid, clientid, invdate, paydate) VALUES
> > > herboris-> (4469858, 2, 'current', 'now');
> > > And still got the same error message:
> > > ERROR: Bad date external representation 'current'
> >
> > 'now' is the only accepted spelling.
> >
> > ('current' used to mean something subtly different from 'now', but that
> > meaning isn't supported anymore.)
>
> I should have mentioned that I'm using version 7.3. As a result of using version 7.3, I'm beginning to realize that my "Practical Postgresql" book isn't quite up to date. The reason I want to use current timestamp constant is because it allows me to calculate the elapsed time between current and now constants (current - now = elapsed_time_in_days) According to the book, "If you watch the...row with the current timestamp, you'll notice it changes in each query to show the updated system time...".(pg. 81). Anyway, since the "current" meaning isn't supported anymore, how would I go about calculating the amount of time (in days) that has elapsed since inserting or updating a field with the initial date.
>
> Thanks for your help,
>
> Mark Tessier

I think you may need to explain to us again what you are trying to achieve
because to insert a record you'd use something like:

INSERT INTO cart (cartid, clientid, invdate, paydate)
VALUES (4469856, 2, current_timestamp, NULL);

to update it you'd use:

UPDATE cart SET paydate = current_timestamp WHERE cartid = 4469856;

and to find out how long it took to be paid and how long ago that was from
current time you'd use:

SELECT paydate - invdate, current_timestamp - invdate
FROM cart
WHERE cartid = 4469856;

The subtle difference that was mentioned was that one timestamp was set at the
start of a transaction and the other was the real current timestamp.

From the sounds of it you're expecting to start a transaction and keep it open
for days, that doesn't seem right.

--
Nigel J. Andrews