Matthew Stuart <[EMAIL PROTECTED]> wrote on 05/01/2004 12:49:03:
> I am trying to create a couple of columns (one createddate and one
> updateddate) in a MySQL table that are DATETIME or TIMESTAMP values,
> but I am having trouble understanding how it works. I want both columns
> to auto add a date and time when a record is first inserted, but only
> the updateddate column to update when the record is updated.
>
> Could someone give me the code for these two columns please? This is
> what I have:
>
> CREATE TABLE all_articles (
> id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
> createddate DATETIME DEFAULT "0000-00-00 00:00:00"
> updateddate DATETIME DEFAULT "0000-00-00 00:00:00"
> article TEXT
> );
>
> Obviously these aren't going to auto add/update because I am not
> stating a NOW() or NULL value anywhere, but I thought this would be the
> cleanest way to give it to you. The way the zeros, hyphens and colons
> are typed is how I would like to store my dates if at all possible.
If the first TIMESTAMP column in a table is defined NOT NULL but is not set
when a new record is added, then it is automatically set to NOW().
Similarly, whenever the record is updated, if it is not explicitly set, it
is overwritten with NOW(). This therefore takes care of your updateddate
column. I think that there is no escape from putting "createddate = NOW()"
in all your INSERT statements.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
