List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:January 5 2004 1:38pm
Subject:Re: struggling newbie - datetime or timestamp problem
View as plain text  






Matthew Stuart <mrs@stripped> 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

Thread
struggling newbie - datetime or timestamp problemMatthew Stuart5 Jan
  • Re: struggling newbie - datetime or timestamp problemrobert_rowe5 Jan
  • Re: struggling newbie - datetime or timestamp problemAlec.Cawley5 Jan
  • RE: struggling newbie - datetime or timestamp problemLarry Brown5 Jan
  • Re: struggling newbie - datetime or timestamp problemTobias Asplund5 Jan