Thread: timestamp error

Hi I have a form that inserts data to the DB. One field is timestamp
type this field is not always filled out but postgres wont accept '' or
null and gives the error (or maybe php gives the error)..

query failed: ERROR: Bad timestamp external representation '' in....

any ideas appreciated

Bob

bob,

> Hi I have a form that inserts data to the DB. One field is timestamp
> type this field is not always filled out but postgres wont accept '' or
> null and gives the error (or maybe php gives the error)..
>
> query failed: ERROR: Bad timestamp external representation '' in....

--
I see two solutions:
1) always add a timestamp with php
maybe (???) this:

   $current=time();            //get current UNIX timestamp

2) use a trigger like this one and remove it from the update/insert clause

CREATE OR REPLACE FUNCTION enterdate() RETURNS trigger AS '
        DECLARE
        BEGIN
                NEW.tstamp := now();
                RETURN NEW;
        END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER mytrig BEFORE INSERT ON t_text
        FOR EACH ROW EXECUTE PROCEDURE enterdate();

INSERT INTO t_text (id) VALUES ('2');

SELECT * FROM t_text;

-- http://dotgeek.org/guruarticles.php?guru=view&id=27
-- may 12, 2004


good luck,
joe speigle
www.sirfsup.com

Hi Bob,

In my tables I just have a default value of now() for my time.
So
CREATE TABLE t1(
c1 text,
time timestamp NOT NULL DEFAULT now()
);

Insert into t1 values ('a');

Colin

>-----Original Message-----
>From: pgsql-novice-owner@postgresql.org
>[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Robert Morgan
>Sent: 20 May 2004 22:56
>To: postgres
>Subject: [NOVICE] timestamp error
>
>
>Hi I have a form that inserts data to the DB. One field is timestamp
>type this field is not always filled out but postgres wont
>accept '' or
>null and gives the error (or maybe php gives the error)..
>
>query failed: ERROR: Bad timestamp external representation '' in....
>
>any ideas appreciated
>
>Bob
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to
>majordomo@postgresql.org
>