On Aug 19, 2007, at 14:04 , Robin Helgelin wrote:
When I started with MySQL I exploited their "bug" with timestamp
fields and always had a entered and updated field on my tables.
As I'm blissfully ignorant of MySQL's peculiarities, without a more
detailed explanation of what you're trying to do, I'm not sure if
this suggestion will help, but here I go anyway:
If you want created and updated timestamps, you can do something like
this:
CREATE TABLE foos
(
foo text PRIMARY KEY
, created_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP
, updated_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO foos (foo) VALUES ('a foo');
SELECT *
FROM foos;
foo | created_at | updated_at
-------+-------------------------------+-------------------------------
a foo | 2007-08-19 15:18:27.271103-05 | 2007-08-19 15:18:27.271103-05
(1 row)
UPDATE foos
SET updated_at = DEFAULT
, foo = 'foo b'
WHERE foo = 'a foo';
SELECT *
FROM foos;
foo | created_at | updated_at
-------+-------------------------------+-------------------------------
foo b | 2007-08-19 15:18:27.271103-05 | 2007-08-19 15:18:35.575783-05
(1 row)
My question, is this interesting information enough to save on the
table itself? If so, I guess this could easily be solved with a
trigger, however, should one instead create a log table and log
changes, etc?
As you mention, you could use a trigger instead of explicitly setting
updated_at to DEFAULT, which might be more convenient because you
don't need remember to set the updated_at column explicitly on update.
Whether or not this information is *interesting* is really up to the
specifics of your application, rather than answerable in a general
sense.
Hope that helps.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
