datetime or timestamp? help please

drewkennelly wrote:
> The person responsibile for the news updates fills out the form with the
> required details - title, body and then when they click 'add' the article is
> saved to the database. At this point I would like the date and time to
> automatically be stored in the database as well (in a field called 'date'
> funnily enough :) )
>
> I am unsure whether the data type of the field 'date' should be 'datetime' or
> 'timestamp' and furthermore how exactly I would get the database to use the
> current date/time as the value for that field when the article is added.

What you want is a TIMESTAMP data type. As you'll see from the MySQL
reference manual, the first TIMESTAMP column in a table is automatically
updated when you enter NULL:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html

In effect, you don't need to supply any value, and a current timestamp
will be inserted.

Use the DATE_FORMAT() function in your SQL queries to extract the date
in the format you want:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

By the way, you shouldn't use the name of a data type as the name of a
column. You have been lucky with "date" because MySQL has made it an
exception to the rule (basically because so many people make the same
mistake as you). When choosing column names, try to use something that
indicates its meaning. Date is very vague, but "created", "written",
"updated", "arrival", "departure", etc are all good names for date
columns that convey more precisely what they're for.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

On 17 Mar 2007 in macromedia.dreamweaver.appdev, drewkennelly wrote:

> I am unsure whether the data type of the field 'date' should be
> 'datetime' or 'timestamp' and furthermore how exactly I would get
> the database to use the current date/time as the value for that
> field when the article is added.

You don't mention what DBMS you're using, but odds are that what you want
is 'timestamp'. That's the idea behind a timestamp field - it will take
whatever the time value is when the record is added/updated, without any
intervention from you.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php