Обсуждение: Bad timestamp external representation
Hi,
When I try to insert with the "current" constant, as in:
herboris=> INSERT INTO cart (cartid, clientid, invdate, paydate) VALUES
herboris-> (4469858, 2, 'current_timestamp', 'now');
I get the following message:
ERROR: Bad date external representation 'current_timestamp'
This is how my table is set up:
/* --------------------------------------------------------
Table structure for table "cart"
-------------------------------------------------------- */
CREATE TABLE "cart" (
"cartid" int8 NOT NULL,
"clientid" varchar(8) NOT NULL,
"packnum" varchar(20),
"shipcost" numeric(5,2),
"prepinit" varchar(2),
"order_by" varchar(12),
"paytype" varchar(20),
"invdate" date NOT NULL,
"paydate" date,
CONSTRAINT "cart_pkey" PRIMARY KEY ("cartid")
);
GRANT ALL ON "cart" TO "apache";
GRANT ALL ON "cart" TO "mark";
ALTER TABLE "cart" ALTER COLUMN "invdate" SET DEFAULT 'now';
I changed "paytype" to type "timestamp" and that didn't work either. Could someone please tell me what "Bad external
representation"means, and hopefully what I'm doing wrong.
--
Thanks,
Mark
On Mon, 28 Apr 2003, Mark Tessier wrote: > When I try to insert with the "current" constant, as in: > > herboris=> INSERT INTO cart (cartid, clientid, invdate, paydate) VALUES > herboris-> (4469858, 2, 'current_timestamp', 'now'); > > I get the following message: > > ERROR: Bad date external representation 'current_timestamp' current_timestamp shouldn't be quoted AFAIK. It's trying to read the string current_timestamp as a date value.
On Mon, 28 Apr 2003 15:09:46 -0700 (PDT) Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: 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' > On Mon, 28 Apr 2003, Mark Tessier wrote: > > > When I try to insert with the "current" constant, as in: > > > > herboris=> INSERT INTO cart (cartid, clientid, invdate, paydate) VALUES > > herboris-> (4469858, 2, 'current_timestamp', 'now'); > > > > I get the following message: > > > > ERROR: Bad date external representation 'current_timestamp' > > current_timestamp shouldn't be quoted AFAIK. It's trying to read the > string current_timestamp as a date value. > > -- Thanks, Mark
On Mon, 28 Apr 2003, Mark Tessier wrote: > On Mon, 28 Apr 2003 15:09:46 -0700 (PDT) > Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > 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' You didn't give a version, but my 7.3 box gives ERROR: 'CURRENT' is no longer supported for simple inserts to date columns with a quoted current. I'd think the correct sequence of values would be: (4469858, 2, current_timestamp, 'now') since current_timestamp isn't like a string literal, but is a special date value function.
Mark Tessier <m_tessier@sympatico.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.)
regards, tom lane
On Tue, 29 Apr 2003 00:02:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mark Tessier <m_tessier@sympatico.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
"PracticalPostgresql" book isn't quite up to date. The reason I want to use current timestamp constant is because it
allowsme to calculate the elapsed time between current and now constants (current - now = elapsed_time_in_days)
Accordingto the book, "If you watch the...row with the current timestamp, you'll notice it changes in each query to
showthe updated system time...".(pg. 81). Anyway, since the "current" meaning isn't supported anymore, how would I go
aboutcalculating 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
On Tue, 29 Apr 2003 00:02:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mark Tessier <m_tessier@sympatico.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
"PracticalPostgresql" book isn't quite up to date. The reason I want to use current timestamp constant is because it
allowsme to calculate the elapsed time between current and now constants (current - now = elapsed_time_in_days)
Accordingto the book, "If you watch the...row with the current timestamp, you'll notice it changes in each query to
showthe updated system time...".(pg. 81). Anyway, since the "current" meaning isn't supported anymore, how would I go
aboutcalculating 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
On Tue, 29 Apr 2003, Mark Tessier wrote:
> On Tue, 29 Apr 2003 00:02:20 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Mark Tessier <m_tessier@sympatico.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
allowsme to calculate the elapsed time between current and now constants (current - now = elapsed_time_in_days)
Accordingto the book, "If you watch the...row with the current timestamp, you'll notice it changes in each query to
showthe updated system time...".(pg. 81). Anyway, since the "current" meaning isn't supported anymore, how would I go
aboutcalculating 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
On Tue, 29 Apr 2003 20:18:40 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk> wrote:
> On Tue, 29 Apr 2003, Mark Tessier wrote:
>
> > On Tue, 29 Apr 2003 00:02:20 -0400
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > > Mark Tessier <m_tessier@sympatico.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
allowsme to calculate the elapsed time between current and now constants (current - now = elapsed_time_in_days)
Accordingto the book, "If you watch the...row with the current timestamp, you'll notice it changes in each query to
showthe updated system time...".(pg. 81). Anyway, since the "current" meaning isn't supported anymore, how would I go
aboutcalculating 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.
In the same book (pg. 81), the author explainsh: "...current will always tell you the "current" time when queried,
regardlessof when it was stored to the table."
The example he gives:
CREATE TABLE tasklog
(taskname char(15),
timebegun timestamp,
timefinished timestamp);
INSERT INTO tasklog VALUES
('delivery', 'now', 'current');
INSERT INTO tasklog VALUES
('remodeling', 'now', 'current');
SELECT taskname, timefinished - timebegun AS timespent FROM tasklog;
taskname | timespent
-----------------------
delivery | 00:15:32
remodeling| 00:04:42
I guess the way he's doing it saves the step of having to UPDATE the row before doing the SELECT calculation. Actually,
Iwas implementing something along the lines of your suggestion since 'current' now seems to be deprecated.
>
> From the sounds of it you're expecting to start a transaction and keep it open
> for days, that doesn't seem right.
Yes, for a given customer who pays by cheque, I want to set the start date and leave the paydate NULL. This way I can
findout how many days have gone by since the invoice date. To do this I would:
Determine if paydate = NULL
If yes, UPDATE paydate with 'current-timestamp'
Do the SELECT calculation to get the number of days since invdate was set
UPDATE paydate with NULL again
When the cheque is received, I would permanently update paydate with NULL.
I hope I'm not being too verbose. In any case it helps me set it straight by having to write it down.
If you, by any chance, have a better suggestion, I would be happy to hear it.
Mark Tessier