Postgres Pro
Downloads
Home   >   mailing lists

Thread: Automatically updating a timestamp upon UPDATE query?

Automatically updating a timestamp upon UPDATE query?

From
Ed Loehr
Date:
How do you most easily create a timestamp column that updates to the
current time on every UPDATE statement?  I know you can use triggers, but
I thought there was a default value that did this for you.  'current'
does not seem appropriate, nor have I made it work (see below for my
example).

    http://www.postgresql.org/docs/postgres/x1137.htm :
    "'now' is resolved when the value is inserted, 'current' is
     resolved everytime the value is retrieved."

Any clues?

Regards,
Ed Loehr

BTW, here's the sequence I used to conclude 'current' doesn't seem to
work:

DROP TABLE foo;
CREATE TABLE foo (
    note    VARCHAR,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT 'current'
);

SELECT now();
INSERT INTO foo (note) VALUES ('Default timestamp test record');
SELECT created FROM foo;
SELECT now();
SELECT * FROM foo;

--  Wait a few seconds for the time to clearly change seconds...

SELECT now();
UPDATE foo SET note = 'Did the update_time change with an UPDATE query?';
SELECT now();
SELECT * FROM foo;
SELECT updated FROM foo;

Time Formats

From
Jeffery L Post
Date:
Is there any way to have a time datatype returned in various formats?

ex. 1:30 PM

Maybe by setting a session variable?

Thanks
Jeff Post

Re: Time Formats

From
Bruno Wolff III
Date:
On Fri, Jan 12, 2001 at 02:02:28PM -0600,
  Jeffery L Post <postjeff@uwm.edu> wrote:
> Is there any way to have a time datatype returned in various formats?
>
> ex. 1:30 PM
>
> Maybe by setting a session variable?

You can use to_char in a query to convert a date and/or time into a character
string in the format you want.

Re: Time Formats

From
Jeffery L Post
Date:
On Fri, 12 Jan 2001, Bruno Wolff III wrote:

> On Fri, Jan 12, 2001 at 02:02:28PM -0600,
>   Jeffery L Post <postjeff@uwm.edu> wrote:
> > Is there any way to have a time datatype returned in various formats?
> >
> > ex. 1:30 PM
> >
> > Maybe by setting a session variable?
>
> You can use to_char in a query to convert a date and/or time into a character
> string in the format you want.
>

I looked up the Function to_char it expects two passed variable COL and
MASK. What is the format of the MASK that it expects and could you give an
example of the use of this funcion in a query that returns multiple
columns.

Re: Time Formats

From
Bruno Wolff III
Date:
On Fri, Jan 12, 2001 at 04:47:35PM -0600,
  Jeffery L Post <postjeff@uwm.edu> wrote:
> On Fri, 12 Jan 2001, Bruno Wolff III wrote:
>
> I looked up the Function to_char it expects two passed variable COL and
> MASK. What is the format of the MASK that it expects and could you give an
> example of the use of this funcion in a query that returns multiple
> columns.

There is a lot of detail in the html documention on the different values
you can use in the mask. Here is a small example:
area=> select to_char(now(),'MM-DD HH:MM'), to_char(now(),'YYYY-MM');
   to_char   | to_char
-------------+---------
 01-12 05:01 | 2001-01
(1 row)