From: | "Joel Burton" <joel(at)joelburton(dot)com> |
---|---|
To: | "Rory Campbell-Lange" <rory(at)campbell-lange(dot)net> |
Cc: | "Postgresql Novice List" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: auto update dates |
Date: | 2002-05-23 16:45:06 |
Message-ID: | JGEPJNMCKODMDHGOBKDNMEFICPAA.joel@joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> Hi Joel.
>
> [For other readers: I'm trying to learn how to update the modified date,
> time and timestamp fields in my database - I've deleted the old parts of
> the mail as it was getting too long.]
>
> Thanks a stack for your mail. I've just about got it working! I have a
> problem with time typing - if I make this function to just update the
> date it does not work.
>
> create function update_date_time() returns opaque as
> 'begin
> new.d_modified = current_date;
> new.t_modified = current_time;
> new.ts_modified = current_timestamp;
> return new;
> end' language plpgsql;
>
> create trigger ideas_update_date_time
> before update
> on ideas
> for each row execute procedure update_date_time();
>
> brandf=> update ideas set title = 'three' where title ~ 'one';
> NOTICE: Error occurred while executing PL/pgSQL function
> update_date_time
> NOTICE: line 3 at assignment
> ERROR: Bad time external representation '16:11:45.820720+01'
current_time returns timezone information, but you're trying to store this
in a field that is time w/o timezone information. either coerce this data
yourself, or make the field hold timezone info.
Better question, though: why are you storing the date, the time, and the
timestamp? Much easier to simply store the timestamp. You can always get the
date & time from that. This will save you space _and_ time. (Plus, your
current problem would become irrelevant).
- J.
Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2002-05-23 17:31:04 | query problem - get count in related table |
Previous Message | Rory Campbell-Lange | 2002-05-23 16:04:25 | Re: auto update dates |